select empty and ... 3

[codrutza]

I work with SQL Server 2000. Pls help

I have 2 tables. I link them with outer join. I have in the second table via:
sea or
air or
road or
'' - empty

How do I retrieve data, in a select, where
via='sea' or via='' ?
If I try not(via='road' or via='air') this brings me only sea, the outer join doesn't take effect.

 

[markros]

Try

select T1.*, T2.* from
Table1 T1 LEFT JOIN Table2 T2 on T1.Pk = T2.FK and T2.Via IN ('sea','air')

See this blog post

link

http://beyondrelational.com/blogs/n...-t-bring-all-records-from-the-left-table.aspx

]Why LEFT JOIN doesn't bring records from the LEFT table[/url]



PluralSight Learning Library

 

[codrutza]

I need to retrieve records with via sea, via empty

Having this:
j0 road
j1 sea
j2 sea
j3
j4 air
j5 road
j6
j7 air
j8 sea

I want the result:
j1 sea
j2 sea
j3
j6
j8 sea

 

[markros]

Change IN ('sea','air') to IN ('sea','')

PluralSight Learning Library

 

[sparkbyte]

I know he said 'sea' or empty but I have seen quite a few of these where empty realy is '' or null.

wouldn't this be more accurate?

From Mark

select T1.*, T2.* from
Table1 T1 LEFT JOIN Table2 T2 on T1.Pk = T2.FK and T2.Via IN ('sea','air',[red]'') OR T2.Via Is Null[/red]

Thanks

John Fuhrman

 

[SQLBill]

The question that you need to figure out and answer for us is what is the value of the 'empty' field. Is it a space, blank, or a NULL?

Markos' suggestion will return it if it is blank. If it is a space, you need to do IN ('sea',' ') and if it is NULL, you will need to do T2.Via ='sea' or T2.Via IS NULL.

I think that's all of the possible options and solutions.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[sparkbyte]

OK, to account for all discussed so far...


How about this. :-D

SELECT T1.*, T2.* 
  FROM Table1 T1 
    LEFT JOIN Table2 T2 on T1.PK = T2.FK 
     AND RTrim(LTrim(T2.Via)) IN ('sea','air','') 
     OR T2.Via IS NULL

Thanks

John Fuhrman

 

[codrutza]

Thank you SQLBill, sparkbyte; good observation null/blank.
Thank you Markros for the AND/WHERE. I know I'll use it again. I didn't know makes such a difference.

 

[SQLBill]

You are welcome...just remember there is a difference between these two:

'' and ' '

The first is for a blank and the second is for a space.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

You are welcome...just remember there is a difference between these two:

'' and ' '

The first is for a blank and the second is for a space.

SQLBill,

When SQL Compares Strings, trailing spaces are removed. So, checking for an empty string would also return strings with a space (or multiple spaces).

Declare @Temp Table(Data VarChar(20), Description VarChar(20))

Insert Into @Temp Values('', 'Empty String')
Insert Into @Temp Values(' ', 'Space')
Insert Into @Temp Values('  ', 'Double Space')
Insert Into @Temp Values(NULL, 'NULL')

Select * From @Temp Where Data = ''

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom