[SQL] Query met groeperen in SELECT

[Christiaan]

Ik heb een vrij complexe query die ik hier verder niet zal posten omdat het voor de vraag niet zo uitmaakt. Het idee is in elk geval dat ik de producten laad uit een product-tabel. Nu kan een product behoren tot geen of meer groepen van producten, en in de overzichten op de site wil ik niet alleen de producten in een lijstje geven, maar in een kolom daarachter ook de groepen waar ze toe behoren. Nu kan dit uiteraard met 1 grote query die alle producten ophaalt, en vervolgens wat code die daar doorheen loopt en per product een nieuwe query uitvoert om daar de groepen bij te halen en die achter elkaar te plakken in een string en die in de kolom achter het product te zitten (wat een zin dit...). Maar in mijn drang naar zo weinig mogelijk code, wil ik weten of het mogelijk dit in 1 query te krijgen.

Het idee is dan als volgt:

SELECT producten.productID, producten.naam, JOIN(groepen.naam, ",")
FROM producten, productgroepen, groepen
WHERE producten.productID = productgroepen.productID
AND productgroepen.groepID = groepen.groepID
GROUP BY producten.productID, producten.naam

Nu heb ik dit in Access ooit zo voor elkaar gekregen meen ik, maar SQL Server wil dit niet toestaan. Is er een andere mogelijkheid om hetzelfde voor elkaar te krijgen zonder dat daar extra queries voor nodig? Een ander alternatief is niet te groeperen, en eventueel producten vaker te laten voorkomen in de query output (per groep een keer) en in de code te zorgen dat dubbele producten bij elkaar blijven. Maar dit moet toch makkelijker kunnen?

[Christiaan]

Hmm. Zit ik een dag op dit probleem te kraken, besef ik me net dat het eigenlijk niet eens zo'n punt is als producten gewoon dubbel in de lijst staan - dus een keer per groep. Dus voor dit probleem is het niet zo belangrijk meer, maar ik ben toch wel benieuwd of er een makkelijke oplossing voor is. Ik kom wel vaker dit soort problemen tegen.

[P_de_B]

Dit is iets wat in mijn ogen niet thuis hoort in een SQL Query. De database moet de gegevens ophalen, dat kan met onderstaande (doorsnee) query.

code:

SELECT producten.productId, producten.naam, ISNULL(groepen,naam,'geen groep') as groep
FROM producten
LEFT OUTER JOIN productgroepen ON producten.productID = productgroepen.productID
LEFT OUTER JOIN groepen ON productgroepen.groepID = groepen.groepID

Dat jij alle groepen in een string achter elkaar wilt hebben is iets dat je in de clientside code moet doen. Wat je niet moet doen is alle producten ophalen en per product een nieuwe query uitvoeren voor de groepen. Gewoon bovenstaande code gebruiken en dan in de clientcode zorgen voor de goede weergave

EDIT:

Christiaan schreef op maandag 10 januari 2005 @ 20:21:
Hmm. Zit ik een dag op dit probleem te kraken, besef ik me net dat het eigenlijk niet eens zo'n punt is als producten gewoon dubbel in de lijst staan - dus een keer per groep.

Precies, dat is de bedoeling.

Je zou trouwens met iets als dit (onderaan) nog wel iets kunnen, maar volgens mij hoort het gewoon aan de clientkant thuis.

[ Voor 27% gewijzigd door P_de_B op 10-01-2005 20:30 ]

[whoami]

Ik ben het eens met P_de_B: met SQL haal je de data gewoon op; hoe je ze presenteert, is geen taak die je met SQL moet doen. De presentatie is de verantwoordelijkheid van iets anders.

(Aan de andere kant: je kan wel een User Defined Function maken die die categorieren per product ophaalt, en dan die UDF aanroepen in je query).

[Christiaan]

whoami schreef op maandag 10 januari 2005 @ 21:02:
Ik ben het eens met P_de_B: met SQL haal je de data gewoon op; hoe je ze presenteert, is geen taak die je met SQL moet doen. De presentatie is de verantwoordelijkheid van iets anders.

Normalitair zou ik het volledig met je eens zijn, maar in dit specifieke geval (en ik heb er nog twee gehad) is dat gewoon verschrikkelijk onpraktisch en langzaam. Alle regels voor nette en gescheiden code/lagen ten spijt; dan moet het maar op een wat 'viezere' wijze.

Er is namelijk generieke code die zoekresultaten in een net overzichtje zet, waarop je dan kunt klikken. Per object in het systeem zijn de overzichten gelijk, en worden ook door dezelfde code gegenereerd. Maar bij alle andere objecten kan men maar 1 categorie per object kiezen (dus zit er in die tabel gewoon een kolom voor de categorieID). Bij producten is dat dus anders, en heb je veel-op-veel relaties met een tussen-tabel. Dan is dus de vraag hoe je de output op zijn wijze netjes krijgt, dus met categorieen, zonder dat je in de generieke handler allemaal uitzonderingen moet schrijven.

[ Voor 35% gewijzigd door Christiaan op 10-01-2005 23:21 ]

[whoami]

Maak dan een user defined functie die er ongeveer zo uit ziet:

code:

CREATE FUNCTION get_categories_for_product( @productid INT )
RETURNS VARCHAR
AS
BEGIN

    DECLARE CURSOR x FOR
    SELECT * FROM categories
    INNER JOIN product_categories ON categories.category_id = product_categories.cat_id
    WHERE product_categories.product_id = @productid

    -- overloop de cursor en zet alles in een string, met comma's ertussen.
    -- return de string dan.

END

Zoiets, ik ben wel ff te lui om op te zoeken hoe dat nu weer met die cursor moet.

Je kan dan je query zo schrijven:

code:

select product.product_name, get_categories_for_product (product.product_id)
from products

[P_de_B]

Doe het dan zo, zonder cursor:

code:

CREATE FUNCTION get_categories_for_product( @productid INT )
RETURNS VARCHAR(250)
AS
BEGIN

    DECLARE @String VARCHAR(1000)

    SELECT @String = COALESCE(@groepen.naam) + ', ', '') + CAST(groepen.naam as VARCHAR(20))
    FROM producten
    LEFT OUTER JOIN productgroepen ON producten.productID = productgroepen.productID
    LEFT OUTER JOIN groepen ON productgroepen.groepID = groepen.groepID
    WHERE producten.productId = @ProductId
   RETURN (@String )

END

En dan aanroepen met

code:

SELECT DISTINCT producten.productId, producten.naam, get_categories_for_product(producten.productID) as Groep
FROM producten

[ Voor 18% gewijzigd door P_de_B op 11-01-2005 08:59 ]

[whoami]

Maar dan ben je toch beperkt tot 2 groepen ?

[P_de_B]

whoami schreef op dinsdag 11 januari 2005 @ 09:14:
Maar dan ben je toch beperkt tot 2 groepen ?

Nee hoor

Test onderstaande maar eens

code:

USE PUBS
GO

DECLARE @allAuthors VARCHAR(500)
SELECT @allAuthors = COALESCE(@allAuthors + ', ', '') + CAST(au_lname as varchar(20))
FROM Authors
PRINT @allAuthors

[Christiaan]

Goede ideeen heren! Ik ga het zsm proberen. Natuurlijk hoop ik het niet meer nodig te hebben, maar just in case

[whoami]

Hmm, nice....

[curry684]

whoami schreef op dinsdag 11 januari 2005 @ 09:14:
Maar dan ben je toch beperkt tot 2 groepen ?

Dit is een van de toptrucs uit 'The Guru's Guide to Transact-SQL', het misbruiken van de rowbased processing van setbased queries om creatieve resultaten te behalen

[P_de_B]

curry684 schreef op dinsdag 11 januari 2005 @ 10:07:
[...]

Dit is een van de toptrucs uit 'The Guru's Guide to Transact-SQL', het misbruiken van de rowbased processing van setbased queries om creatieve resultaten te behalen

offtopic:
Ken Henderson roels