Join Table with No Data - 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

Transact-SQL (2000)

Join Table with No Data

Author

Topic

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 19:13:50

Hey all. I just tried running a query with inner join on two tables. TableA has some rows and TableB has rows as well. Each row in TableA may or may not have a row in TableB associated with it. When I run a query to return a particuluar record based on the column i choose in TableA, it should show me the data in TableA and any data in TableB if it has any. I'm only familiar with inner joins, and i think this may be something of outer join, left outer or right or whatever kinda joins. I keep reading on those, but I never quite truely grasp the idea/uses behind those without true examples, since I have never ran into a situation needing them.

- RoLY roLLs

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-29 : 19:20:48

This would be a left outer join.

SELECT column_list
FROM TableA a
LEFT OUTER JOIN TableB b ON a.id = b.id

INNER JOIN - returns where you have matches in both columns
LEFT JOIN (left outer join) - returns all of left (TableA) and right (TableB) where it matches
RIGHT JOIN (right outer join) - returns all of right (TableB) and left (TableA) where it matches

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.