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
 General SQL Server Forums
 New to SQL Server Programming
 Tricky Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barnabeck
Posting Yak Master

Spain
194 Posts

Posted - 01/23/2013 :  17:14:32  Show Profile  Reply with Quote
i have these 3 tables:
Table: PRODTABLE
PRODID     ....
----------------
WO13.....
WO1300013
WO13....

Table: PRODBOM
PRODID      ITEMID   QTY
----------------------------
WO13.....
WO1300013   123456   10.12
WO1300013   246802   8.58
WO1300013   345678   2.00
WO1300013   123849   4.00
WO1300013   111111   0.50
WO1300013   222222   1.00

Table: ColorCoding
ITEMID     TAG
--------------------
6764389    #123456
7859869    #234567
1111111    #aa33cc
5647358    #bb44dd
2222222    #dd11aa


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.TAG
There 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
PRODID       TAG
-----------------
WO13.....
WO1300013   #aa33cc
WO13....


I'm puzzeling but do not get it,
Martin

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 01/23/2013 :  17:49:10  Show Profile  Reply with Quote
Perhaps this?
SELECT
	p.prodid,
	a.tag
FROM
	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);
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

406 Posts

Posted - 01/23/2013 :  17:59:24  Show Profile  Reply with Quote
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
Go to Top of Page

barnabeck
Posting Yak Master

Spain
194 Posts

Posted - 01/24/2013 :  11:28:59  Show Profile  Reply with Quote
@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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 01/24/2013 :  12:47:56  Show Profile  Reply with Quote
You are very welcome - glad to be of help.
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