joining with an empty table - SQL Server Forums

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)

joining with an empty table

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2003-03-24 : 15:32:36

My table structures:
AccountDist Table
lngAccountLevel txtDescription lngStartPos lngLength
11 Location 1 5
12 Division 6 5
13 Section 11 10

FreeKeyAlloc Table
lngProfileID txtSegment blnFreeKey
2 Location 1
2 Division 1
2 Section 1
2 ACS 1 0
2 ACS 2 1
2 ACS 3 1

The SP below returns these data:
Location 5 1 1
Division 5 6 1
Section 10 11 1

SELECT A.txtDescription, A.lngLength, A.lngStartPos, F.blnFreeKey, A.lngAccountLevel
FROM dbo.AccountDist A left outer join dbo.FreeKeyAlloc F on A.txtDescription = F.txtSegment
WHERE (A.lngAccountLevel > 10)
AND (F.lngProfileID = 1)
ORDER BY A.lngAccountLevel ASC

However, there will be times when the FreeKeyAlloc table would be empty (no record) and I still want the SP return the data in the AccountDist table. How do I rewrite the SP to cover both situations (FreeKeyAlloc table with or without records in it)?

Thanks.

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-24 : 17:15:16

SELECT A.txtDescription, A.lngLength, A.lngStartPos, F.blnFreeKey, A.lngAccountLevel
FROM dbo.AccountDist A
left outer join dbo.FreeKeyAlloc F on A.txtDescription = F.txtSegment
WHERE (A.lngAccountLevel > 10) AND (F.lngProfileID = 1 OR F.lngProfileID IS NULL)
ORDER BY A.lngAccountLevel ASC

Maybe if you change the WHERE condition on the element in F to include NULL?

HTH

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-24 : 17:44:22

SELECT A.txtDescription, A.lngLength, A.lngStartPos, F.blnFreeKey, A.lngAccountLevel
FROM dbo.AccountDist A
left outer join dbo.FreeKeyAlloc F
on A.txtDescription = F.txtSegment AND
F.lngProfileID = 1
WHERE A.lngAccountLevel > 10
ORDER BY A.lngAccountLevel ASC

If you have a an outer table in a join, NEVER include that table in your WHERE clause. it turns it into an INNER JOIN. put the criteria in with the join.

(also: don't prefix your column names with the datatype; that's SOOOO Visual basic 2.0!!!

)

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources