SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 help on SELECT query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

getcarter15
Starting Member

United Kingdom
4 Posts
Posted - 05/28/2012 :  11:11:46  Show Profile  Reply with Quote
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

India
47099 Posts
Posted - 05/28/2012 :  13:28:25  Show Profile  Reply with Quote
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/
Go to Top of Page

getcarter15
Starting Member

United Kingdom
4 Posts
Posted - 05/29/2012 :  04:46:39  Show Profile  Reply with Quote
awesome! thanks Visakh16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 05/29/2012 :  12:10:39  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000