Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
I want exactly the same records from Prodtable (no repetition for PRODID) and add to each record a color tag in case that the following match is true:One of the related items, listed in PRODBOM with the same PRODID is also member of the ColorCoding Table. In this case I want the Color Tag to be returned together with the PRODID.There might be no match, so NULL is given to Relationship.TAGThere might be more matches in PRODBOM (222222 and 111111 are both in ColorCoding Table); in this case ONLY the ITEMID with the smaller QTY is considered for a possible match with ColorCoding Table.So in this case it would be returned
SELECT p.prodid, a.tagFROM prodtable p OUTER APPLY ( SELECT TOP (1) tag FROM ColorCoding c INNER JOIN ProdBOM b ON b.itemId = c.ItemId WHERE b.prodid = p.prodid ORDER BY qty ASC ) a(tag);
bitsmed
Aged Yak Warrior
545 Posts
Posted - 2013-01-23 : 17:59:24
or maybe this:
select a.prodid ,e.tag from prodtable as a inner join (select b.prodid ,min(itemid) as itemid from (select prodid ,min(qty) as qty from prodbom group by prodid ) as b inner join prodbom as c on c.prodid=b.prodid and c.qty=b.qty ) as d on d.prodid=a.prodid left outer join colorcoding as e on e.itemid=d.itemid
barnabeck
Posting Yak Master
236 Posts
Posted - 2013-01-24 : 11:28:59
@James: your query worked for me! Thank you so much!@Bitsmed: the way you put the query it doesn't work. b.prodid is missing in the group clause! After adding it (right before the d-table bracket is closed), the amount of records doesn't match the expected one and so I stick to solution proposed by James. Thank you anyway!!!Martin