SQL Null values -- WHERE problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Null values -- WHERE problem

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2003-07-03 : 04:40:12

I'm in the process of converting ACCESS queries to SQL views + stored procedures and have run into a problem. What I'm trying to do is as follows:

1) SELECT all records from TABLE1
2) JOIN to TABLE2 and TABLE3
3) Only pickup certain records in TABLE2.

SELECT Table1.FldA, Table2.FldB, Table3.FldC,
CASE <-- not sure this is the best
WHEN Table2.FldB = NULL then '' way to handle this
ELSE
'Y'
END AS FldExists
FROM (Table1 LEFT JOIN Table2 ON Table1.FldA = Table2.FldB)
LEFT JOIN Table3 ON Table1.FldD = Table3.FldC
WHERE Table2.FldE = @ZUserID OR Table2.FldE Is Null
ORDER BY Table1.FldA

This works in ACCESS but I get different results in SQL, not all the records appear from Table1. I'm assuming that I need to use a Computed field at the SELECT level, but I'm not sure how to do that.
Please help. Thanks.
ORDER BY Table1.FldA

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-03 : 05:31:09


SELECT Table1.FldA, Table2.FldB, Table3.FldC,
  CASE  
  When table2.fldb is null 
then ''       
  ELSE
     'Y'
  END AS FldExists 
FROM 
 Table1 
LEFT JOIN Table2 ON Table1.FldA = Table2.FldB
LEFT JOIN Table3 ON Table1.FldD = Table3.FldC
WHERE (Table2.FldE = @ZUserID OR Table2.FldE Is Null)
ORDER BY Table1.FldA

-- You wont get all the table1 records from this query without 
-- removing the where clause.

-------
Moo.

ingineu
Yak Posting Veteran

89 Posts

Posted - 2003-07-03 : 06:21:59

How do you get the WHERE CLAUSE to apply only to TABLE2?

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-03 : 06:32:45

quote:

How do you get the WHERE CLAUSE to apply only to TABLE2?

Do this

SELECT Table1.FldA, Tb2.FldB, Table3.FldC,
CASE
When tb2.fldb is null
then ''
ELSE
'Y'
END AS FldExists
FROM
Table1

LEFT OUTER JOIN

( SELECT table2.fldb, table2.flde
FROM table2
where Table2.FldE = @ZUserID OR Table2.FldE Is Null
) tb2 on tb2.fldb = Table1.FldA

LEFT JOIN Table3 ON Table1.FldD = Table3.FldC
ORDER BY Table1.FldA

-------
Moo.

ingineu
Yak Posting Veteran

89 Posts

Posted - 2003-07-03 : 07:03:42

Thanks .. works great!!

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-03 : 10:16:04

Well what did you expect, when you MOO, you've got the power!

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources