SELECT Query from multiple tables help please

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

General SQL Server Forums

New to SQL Server Programming

SELECT Query from multiple tables help please

Author

Topic

hurdy
Starting Member

17 Posts

Posted - 2010-03-22 : 19:26:57

Hi everyone,

Thank you for taking the time out to read this message. I'm having some trouble selecting data from two tables and filtering out some of the results.

Simply, I want to pull all the data in a column from one table, then link data from a second table into another column that corresponds to the first table but ignoring certain conditions and to also return NULL values.

I think it would be better for me to show you an example of my tables & data so you can better visualize what I am trying to achieve.

TABLE 1 : "LooseItems"
Column1: "LooseItemID" - Int (ID column and link to table 2)
Colmun2: "LooseItemDescription" - Char

TABLE 2 : "LooseItemsPacked"
Column1: "LooseItemPackedID" - Int
Column2: "UnitID" - Int
Column3: "LooseItemID" - Int - (Is the link between the two tables)
Column4: "Quantity" - Int

My Tables have the following data

TABLE 1: LooseItems
LooseItemID, LooseItemDescription
1, Hat
2, Coat
3, Boots
4, Jumper
5, Socks
6, T-Shirt

TABLE 2: LooseItemsPacked
LooseItemPackedID, UnitID, LooseItemID, Quantity
1, 1, 6, 10
2, 1, 1, 2
3, 2, 6, 5
4, 1, 4, 8
5, 1, 5, 8
6, 2, 1, 2
7, 2, 3, 12

**NOTICE in TABLE 2 how there is only ever 1 link between a UnitID of a given value and a LooseItem. For example UnitID 1 & LooseItemID 6 match will never be repeated again. -- I don't think this knowledge will effect this questions to be honest.

Now I want to query the database for a given UnitID to produce the following result;

QUERY RESULT WANTED - WHERE UnitID = 1
Description, Quantity, UnitID
Hat, 2, 1
Coat, 0, NULL
Boots, 0, NULL
Jumper, 8, 1
Socks, 8, 1
T-Shirt, 10, 1

I have an SQL statement that almost does what I want but as soon as I add a WHERE clause it completely filters my results down stripping out null values.

SELECT LooseItems.LooseItemDescription, ISNULL(LooseItemsPacked.Quantity, 0) AS Quantity, LooseItemsPacked.UnitID
FROM LooseItems LEFT OUTER JOIN LooseItemsPacked ON LooseItems.LooseItemID = LooseItemsPacked.LooseItemID

I need some kind of WHERE clause like UnitID=@UnitID but in a way that will still return all items listed in TABLE 1 even if there isn't a corresponding record in TABLE 2 for a given UnitID.

Thank you for your time any help is much appreciated. Please let me know if I haven't explained myself very well and I will try to again.

Thanks again,

Rob.

singularity
Posting Yak Master

153 Posts

Posted - 2010-03-22 : 20:55:47

Instead of putting your condition in the WHERE clause, put it in the JOIN, like so:


SELECT LooseItems.LooseItemDescription, ISNULL(LooseItemsPacked.Quantity, 0) AS Quantity, LooseItemsPacked.UnitID
FROM LooseItems 
LEFT OUTER JOIN LooseItemsPacked ON LooseItems.LooseItemID = LooseItemsPacked.LooseItemID AND
                                    LooseItemsPacked.UnitID = 1

hurdy
Starting Member

17 Posts

Posted - 2010-03-22 : 21:02:01

Excellent, thank you singularity it works perfectly!

Well done and thank you ever so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 10:09:59

reason is this

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources