Trouble with query. Kind of lost here

Question

Post reply

Trouble with query. Kind of lost here

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Something like this should do the trick:

[font="Courier New"]DROP TABLE #Order

CREATE TABLE #Order (Client VARCHAR(3), Product VARCHAR(3))

INSERT INTO #Order (Client, Product)

SELECT 'XXX','001' UNION ALL -- B

SELECT 'XXX','003' UNION ALL -- C

SELECT 'XXX','007' UNION ALL -- A

SELECT 'XXX','009' UNION ALL -- A

SELECT 'YYY','001' UNION ALL -- B

SELECT 'YYY','004' UNION ALL -- C

SELECT 'YYY','005' UNION ALL -- B

SELECT 'YYY','002' UNION ALL -- A,B

SELECT 'ZZZ','002' UNION ALL -- A,B

SELECT 'ZZZ','009' UNION ALL -- A

SELECT 'ZZZ','008' UNION ALL -- B

SELECT 'ZZZ','007' -- A

DROP TABLE #Combos

CREATE TABLE #Combos (Product VARCHAR(3), Combo VARCHAR(7))

INSERT INTO #Combos (Product, Combo)

SELECT '002','Combo A' UNION ALL

SELECT '007','Combo A' UNION ALL

SELECT '009','Combo A' UNION ALL

SELECT '008','Combo B' UNION ALL

SELECT '005','Combo B' UNION ALL

SELECT '001','Combo B' UNION ALL

SELECT '002','Combo B' UNION ALL

SELECT '004','Combo C' UNION ALL

SELECT '003','Combo C'

SELECT *

FROM #Order o

INNER JOIN #Combos c ON c.Product = o.Product

ORDER BY o.Client, o.Product

SELECT o.Client, c.Combo, d.ComboProductsMax, COUNT(*) AS ComboProductsFound

FROM #Order o

INNER JOIN #Combos c ON c.Product = o.Product

INNER JOIN (SELECT Combo, COUNT(*) AS ComboProductsMax FROM #Combos GROUP BY Combo) d ON d.Combo = c.Combo

GROUP BY o.Client, c.Combo, ComboProductsMax

HAVING ComboProductsMax - COUNT(*) < 2

ORDER BY o.Client, c.Combo

[/font]

You might want to wrap this in another SELECT to pick the "most promising" product group per customer.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

romerob Grasshopper Points: 15 More actions · Answer 2

This is great Chris. Thanks a lot.

I knew there was an elegant way to solve it.

Cheers,

Bromero