also display rows that are not associated

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 2005 Forums

Transact-SQL (2005)

also display rows that are not associated

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-08-14 : 05:14:30

If I have two tables like this...

Table 1
PID Int
CatID Int
Product

Table 2
CatID
Category

Lets say that I have 20 items in table 1, but only 13 of them are connected to a catergoryID through the CatID Integer, can I then Select all items that have a specific CatID, but also thoose that does not yet have a CatID?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 05:20:05

LEFT JOIN ??

or presumably CATID in your table1 would just be null?

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 05:33:03

[code]DECLARE @Products TABLE (ProdID INT, CatID INT, ProdName VARCHAR(20))

INSERT @Products
SELECT 1, 1, 'SQLTeam' UNION ALL
SELECT 2, 1, 'Yak' UNION ALL
SELECT 2, NULL, 'Peso'

DECLARE @Categories TABLE (CatID INT, CatName VARCHAR(20))

INSERT @Categories
SELECT 1, 'Dummy' UNION ALL
SELECT 2, 'Another dummy'

SELECT *
FROM @Products AS p
LEFT JOIN @Categories AS c ON c.CatID = p.CatID
WHERE p.CatID = 1
OR p.CatID IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-08-14 : 06:44:48

Your´e the Man Peso, Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources