Tricky 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

General SQL Server Forums

New to SQL Server Programming

Tricky Query

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2013-01-23 : 17:14:32

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-23 : 17:49:10

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);

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-24 : 12:47:56

You are very welcome - glad to be of help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources