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)
 LEFT JOIN Not returning a row???

Author  Topic 

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-11 : 12:46:42
I must be having a rough day, plus I'm getting slammed from every direction from every platform

If I have this query


    SELECT x.Value
      FROM XREF	x
     WHERE x.TableName 		= 'StaffType'
       AND x.ColumnName 	= 'StaffType'

    SELECT x.Value, s.Present, s.Proposed, s.OnBoard
      FROM XREF	x
 LEFT JOIN PIFStaff		s
	ON x.Value		= s.StaffType 
     WHERE x.TableName 		= 'StaffType'
       AND x.ColumnName 	= 'StaffType'
       AND s.PIFRecID IN (11,18)


Why would the Value "Workstations (Staff)" be missing from the 2nd query?


Value                                              
-------------------------------------------------- 
Private Offices (Agent)
Private Offices (Staff)
Workstations (Agent)
Workstations (Staff)

(4 row(s) affected)

Value                                              Present     Proposed    OnBoard     
-------------------------------------------------- ----------- ----------- ----------- 
Private Offices (Agent)                            10          20          25
Private Offices (Agent)                            100         200         300
Private Offices (Staff)                            11          21          26
Private Offices (Staff)                            110         210         310
Workstations (Agent)                               12          22          27
Workstations (Agent)                               120         220         320



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-10-11 : 13:01:42
I guess your IN condition is preventing the missing row from being included in the result set.

I think this should do it:

SELECT x.Value, s.Present, s.Proposed, s.OnBoard
      FROM XREF	x
 LEFT JOIN PIFStaff		s
	ON x.Value		= s.StaffType 
     WHERE x.TableName 		= 'StaffType'
       AND x.ColumnName 	= 'StaffType'
       AND s.PIFRecID IN (11,18) or s.PIFRecID IS NULL


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-11 : 13:03:09
Moved the predicate up to the JOIN


    SELECT x.Value, COALESCE(s.Present,0), COALESCE(s.Proposed,0), COALESCE(s.OnBoard,0)
      FROM XREF	x
 LEFT JOIN PIFStaff		s
	ON x.Value		= s.StaffType 
       AND s.PIFRecID IN (11,18)
     WHERE x.TableName 		= 'StaffType'
       AND x.ColumnName 	= 'StaffType'




OK, so what's going on though?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-11 : 13:04:48
Harsh, Thanks, but that didn't work either because XREF has many other "logical" code tables in there



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-11 : 13:16:50
Huh, I did the same thing for the final query, because I had to use a different key


    SELECT pm.MEPrecID, pm.PIFRecID, x.Value AS StaffType
	, COALESCE(s.Present,0) AS Present, COALESCE(s.Proposed,0) AS Proposed, COALESCE(s.OnBoard,0) AS OnBoard
      FROM XREF			x
 LEFT JOIN PIFStaff	s
	ON x.Value = s.StaffType
INNER JOIN PIF_MEP 	pm
	ON pm.PIFRecID = s.PIFRecID
       AND pm.MEPRecID = 1
     WHERE x.TableName 		= 'StaffType'
       AND x.ColumnName 	= 'StaffType'


And then change it to this and it worked..I'm so confused


    SELECT xxx.MEPrecID, xxx.PIFRecID, x.Value AS StaffType
	, COALESCE(xxx.Present,0) AS Present, COALESCE(xxx.Proposed,0) AS Proposed, COALESCE(xxx.OnBoard,0) AS OnBoard
      FROM XREF			x
 LEFT JOIN ( SELECT pm.MEPrecID, pm.PIFRecID, s.StaffType, s.Present, s.Proposed, s.OnBoard  
	       FROM PIFStaff	s
	 INNER JOIN PIF_MEP 	pm
		 ON pm.PIFRecID = s.PIFRecID
       		AND pm.MEPRecID = 1) AS XXX
	ON x.Value		= xxx.StaffType 
     WHERE x.TableName 		= 'StaffType'
       AND x.ColumnName 	= 'StaffType'



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2007-10-11 : 13:45:30
Does this help at all?

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

A rule of thumb is: unless you are simply doing an "Is Null" check, NEVER reference an outer joined table in your criteria. Always put criteria in the join or use a derived table.

I can't make sense of what works and what doesn't in your last examples -- can you provide some DDL and sample data so you can demonstrate?

UPDATE: Looking at your example again, I see you are doing a LEFT OUTER JOIN to one table, and then from there joining that table via an INNER JOIN to another. That eliminates any NULL values returned from the outer join, so it essentially behaves the same as an INNER JOIN, causing your issue.


- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-10-11 : 14:00:33
quote:
Originally posted by jsmith8858

I can't make sense of what works and what doesn't in your last examples -- can you provide some DDL and sample data so you can demonstrate?


Brett, see this link for how to do it:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx



Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-11 : 14:13:07
D'oh

It was merely a question about JOIN Behaviour though

But I got a chuckle

Still The last on I posted works



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2007-10-11 : 14:41:27
Just for Brett:

http://weblogs.sqlteam.com/jeffs/archive/2007/10/11/mixing-inner-outer-joins-sql.aspx

It is actually a great example and a good topic for post ... hope this helps to clear things up. If not, let me know.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-11 : 14:47:57
Great

Thanks

Jeff



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-10-12 : 02:01:49
Double left join?
SELECT		pm.MEPrecID,
		pm.PIFRecID,
		x.Value AS StaffType,
		COALESCE(s.Present, 0) AS Present,
		COALESCE(s.Proposed, 0) AS Proposed,
		COALESCE(s.OnBoard, 0) AS OnBoard
FROM		XREF AS x
LEFT JOIN	PIFStaff AS s ON s.StaffType = x.Value
LEFT JOIN	PIF_MEP AS pm ON pm.PIFRecID = s.PIFRecID
			AND pm.MEPRecID = 1
WHERE		x.TableName = 'StaffType'
		AND x.ColumnName = 'StaffType'


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2007-10-12 : 07:15:46
Peso -- read the second article I linked to; that is not logically the same...

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site