help on SELECT query - 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 2008 Forums

Transact-SQL (2008)

help on SELECT query

Author

Topic

getcarter15
Starting Member

4 Posts

Posted - 2012-05-28 : 11:11:46

Hi

I'm fairly new to all this and I need some help with a SELECT query please...

I have two tables: the first (Stock) contains a list of stock items, inculding an Item_ID(pk), Description, Item Type etc. One of the item types available is 'Kit' and details of these items are stored in my second table (Kits), which has fields Item_ID and Component_ID. Kit items contain other stock items and both the kit items and their component items are also listed in the Stock table.

These are joined as follows
Kits LEFT JOIN Stock on (Kits.Item_ID = Stock.Item_ID)

For my select query, I need a list of kit items with details of their components so I'm trying to use:

Kits.ItemID AS KitID,
Stock.Description AS KitDescription,
Kits.Componenet_ID AS ComponentID,
CASE WHEN kits.Item_ID = Stock.Item_ID THEN Stock.Description END AS ComponentDescription

but the component description is just giving me null values.

Any suggestions on how i can do this?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 13:28:25

sounds like this


SELECT required columns here -- *
FROM Stock s
INNER JOIN Kits k
ON k.Item_ID = s.Item_ID
AND s.Item_Type='Kits'
LEFT JOIN Stock s1
ON s1.Item_ID = k.Component_ID

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

getcarter15
Starting Member

4 Posts

Posted - 2012-05-29 : 04:46:39

awesome! thanks Visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 12:10:39

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources