[SQL] Join op 1 rij

[YopY]

Google is vandaag niet mijn vriend, aldus dit topic.

Ik heb een query die haalt de 4 meest recent uitgegeven boeken op, en koppelt deze aan de auteur van dit boek dmv een eenvoudige (left) join.

Nu is er bij het ontwerp van dit systeem echter geen rekening gehouden met boeken met meerdere auteurs, waardoor er twee rijen voor 1 boek teruggegeven worden - 1 voor elke auteur.

Met Goegel ben ik niet veel verder gekomen - de daarin gestelde casussen konden vaak simpelweg iets met het hoogste ID eruithalen, maar dit is hier niet op van toepassing - of ik ben gek.

In ieder geval, dit is de huidige query, in vereenvoudigde vorm

SQL:

SELECT          DISTINCT TOP 4 
                prd.EerstePublicatieDatum,
                prd.id as bookId,
                prd.titel AS bookTitle,
                pch.cph_id AS authorId,
                cph.voornaam + ' ' + cph.achternaam AS authorName
FROM            Product prd
LEFT JOIN       ProductCopyrightHouder pch ON pch.prd_id = prd.id
INNER JOIN      copyrighthouder cph ON cph.id = pch.cph_id
ORDER BY        prd.EerstePublicatieDatum DESC

Die de resultaten geeft:

code:

2009-08-29 00:00:00 21584   Verzamelde gedichten    7716    Hagar Peeters
2009-08-29 00:00:00 21584   Verzamelde gedichten    7717    Bart Vonck
2009-08-28 00:00:00 19817   Balotje en het tasje van oma    3131    Yvonne Jagtenberg
2009-08-28 00:00:00 21443   Zoete mond  2935    Thomas Rosenboom

Zoals te zien staat 'Verzamelde Gedichten' hier tweemaal in, omdat deze twee auteurs heeft.

Nu heb ik dit als volgt geprobeerd op te lossen (work in progress, de daadwerkelijke auteur die zichtbaar moet zijn weet ik nog niet), door een subquery in je join te doen die maar 1 auteur ophaalt:

code:

INNER JOIN      (SELECT Id, voornaam, achternaam FROM CopyrightHouder WHERE Id = pch.cph_id) cph ON cph.id = pch.cph_id

Maar zoals doorgewinterde SQL mensen misschien wel weten geeft dit de fout dat:

code:

The column prefix 'pch' does not match with a table name or alias name used in the query.

Dusch. Ik zit een beetje vast - welke oplossingen zijn er nog meer? Behalve het ombouwen van de presentatielaag zodat die rekening houdt met meerdere rijen met boeken natuurlijk - dat zal veel te lang duren, lijkt mij. Uw hulp zou zeer welkom zijn. Ideaal zou zijn als dit allemaal gewoon in 1 query kan, zonder dat dit al te zwaar op de performance gaat drukken.

(ps, de software is MSSQL, volgens mij 2005)

[P_de_B]

De belangrijkste vraag is, wat wil je zien in de kolom 'auteur' als er meerdere zijn?

In principe zou ik zeggen dat dit werk voor de presentatielaag is. Uiteraard is het best mogelijk dit in de query op te lossen, maar dan zul je wel moeten aangeven wat je wilt zien.

[RobIII]

^^ Met hem...

YopY schreef op dinsdag 01 september 2009 @ 14:31:
Behalve het ombouwen van de presentatielaag zodat die rekening houdt met meerdere rijen met boeken natuurlijk - dat zal veel te lang duren, lijkt mij.

Dat is ook geen rocket science; maar is natuurlijk afhankelijk van hoe je presentatielaag in elkaar zit en hoe complex die is.

Feitelijk komt 't neer op:

code:

records = DB.execute("select * from books left outer join ... where ... order by pubdate desc, id")
curbook = null
foreach row in records {
  if ((curbook == null) || (curbook.id <> row.id))
    curbook = new book(row.id, row.title, row.isbn, row.author, ...)
    books.add curbook
  else
    curbook.author += ", " + row.author
}
//books collection bevat nu elk boek maar 1 keer

[YopY]

Juist, dan zal ik daar even naar kijken. Ik dacht dat dit de beste 'quick & dirty' oplossing was, maar aangezien ik er al een paar uur in heb zitten betwijfel ik het. Ik zal de presentatie dan zien aan te passen, bedankt.

[Verwijderd]

Ben zelf meer van de MySQL omgeving (waar we dus gewoon GROUP_CONCAT voor dit soort dingen hebben) maar vond hier een benadering van die functie in SQL Server 2005. Heb hem even omgeschreven naar jouw situatie, maar niet getest, so don't shoot me

code:

SELECT 
    prd.EestePublicatieDatum,
    prd.id AS bookId,
    prd.titel AS bookTitle, 
    LEFT (authorNames,LEN(authorNames) - 1) AS authorNames
FROM Product prd
    CROSS APPLY (
        SELECT cph.voornaam + ' ' + cph.achternaam + ', '
        FROM ProductCopyRightHouder pch
        INNER JOIN copyrighthouder cph ON cph.id = pch.cph_id 
        WHERE pch.prd_id = prd.id
        FOR XML PATH('')  
    ) pre_trimmed (authorNames)
GROUP BY bookId,authorNames

[RobIII]

Ik denk dat ik liever mijn presentatielaag aanpas dan dat ik die ranzige constructie zou gebruiken What the hell?

Een GROUP_CONCAT is al smerig goedje en zou verboden moeten worden; maar het gedrocht van een query wat je hier presenteert... No thanks... Dit zijn nou eenmaal dingen waar RDBMS'en feitelijk niet voor bedoeld zijn; vandaar ook zulke ranzige hacks. Dit is gewoon presentatielaag werk.

[ Voor 44% gewijzigd door RobIII op 02-09-2009 23:58 ]

[Verwijderd]

RobIII schreef op woensdag 02 september 2009 @ 18:59:
Ik denk dat ik liever mijn presentatielaag aanpas dan dat ik die ranzige constructie zou gebruiken What the hell?

Een GROUP_CONCAT is al smerig goedje en zou verboden moeten worden; maar het gedrocht van een query wat je hier presenteert... No thanks... Dit zijn nou eenmaal dingen waar RDBMS'en feitelijk niet voor bedoeld zijn; vandaar ook zulke ranzige hacks. Dit is gewoon presentatielaag werk.

Akkoord, misschien wel, maar een GROUP_CONCAT voor vier rows is nou niet bepaald verschrikkelijk...

[Janoz]

Verwijderd schreef op donderdag 03 september 2009 @ 10:28:
[...]


Akkoord, misschien wel, maar een GROUP_CONCAT voor vier rows is nou niet bepaald verschrikkelijk...

Owh? Waar wel dan? Bij 5, of bij 20?

IMHO is ie zelfs bij 2 al smerig. Dit soort dingen hoor je imho af te handelen in je business logic laag of view laag.

[RobIII]

Verwijderd schreef op donderdag 03 september 2009 @ 10:28:
een GROUP_CONCAT voor vier rows is nou niet bepaald verschrikkelijk...

Daar gaat het niet om; al was 't maar 1 row... dit soort zaken hoor je gewoon in je presentatielaag af te handelen. En true, een GROUP_CONCAT zal je de nek niet kosten en zeker niet op 4 rows. Het is het feit dat 't ding uberhaupt bestaat En dat is dan nog altijd andere koek dan de 'constructie' waar jij mee op de proppen kwam; die is just plain nasty

[Big Womly]

En dit?

SQL:

select prd.EerstePublicatieDatum, 
       prd.id as bookId, 
       prd.titel AS bookTitle, 
       pch.cph_id AS authorId, 
       cph.voornaam + ' ' + cph.achternaam AS authorName 
from ( select distinct top 4
              EerstePublicatieDatum, 
              id,
              titel
       from Product
     )
LEFT JOIN  ProductCopyrightHouder pch ON pch.prd_id = prd.id
INNER JOIN copyrighthouder cph ON cph.id = pch.cph_id
ORDER BY   prd.EerstePublicatieDatum DESC 

En als je dan 1 record per boek wilt hebben en alle auteurs in kolommen erachter, dan kun je via PL/SQL en een count() de rijen naar kolommen omzetten

[ Voor 16% gewijzigd door Big Womly op 03-09-2009 10:51 ]

[Verwijderd]

RobIII schreef op donderdag 03 september 2009 @ 10:38:
[...]
die is just plain nasty

Mooi, daar ging ik voor... D

Maar goed, jullie hebben absoluut gelijk dat dit niet op DB-niveau gebeuren moet. Daar ga ik absoluut niet tegenin (kan ik ook niet ).

[curry684]

RobIII schreef op donderdag 03 september 2009 @ 10:38:
[...]

Daar gaat het niet om; al was 't maar 1 row... dit soort zaken hoor je gewoon in je presentatielaag af te handelen. En true, een GROUP_CONCAT zal je de nek niet kosten en zeker niet op 4 rows. Het is het feit dat 't ding uberhaupt bestaat En dat is dan nog altijd andere koek dan de 'constructie' waar jij mee op de proppen kwam; die is just plain nasty

Hmm in het kader leuke discussies moet ik zeggen dat ik GROUP_CONCAT een hele mooie nette constructie vind die ik graag ook in andere DBMS'en zou zien. Waarom zou het een ranziger aggregate function zijn dan max, min, stddev of count?

[Kalentum]

Ik ben ook wel benieuwd waarom GROUP_CONCAT evil is. Het is SUM() maar dan voor strings. Ik gebruik het voor bijvoorbeeld rapportages.

[Woy]

Ik zie het probleem er ook niet direct in. Niet zozeer in GROUP_CONCAT op zichzelf, maar waarom zou je niet zelf Aggregate functions kunnen declareren ( Zou dat overigens ook niet in MS-SQL komen? Volgens mij is het in Oracle al mogelijk )

in principe zou elke functie die er als volgt uitziet voldoen

C#:

delegate T Aggregator<T>(IEnumerable<T> items);

Zo zou je zelfs een Random aggregate function kunnen maken. Niet dat het vaak nuttig zou zijn, maar je kan IMHO beter

SQL:

SELECT Id, Random( SomeOtherField ), Max( SomeField )
FROM SomeTable
GROUP BY Id

hebben dan de constructie in MySql

SQL:

SELECT Id, SomeOtherField, Max( SomeField )
FROM SomeTable
GROUP BY Id

In het eerste voorbeeld is ten minste duidelijk dat je niet zeker weet welke waarde uit SomeOtherField je krijgt.

[ Voor 5% gewijzigd door Woy op 03-09-2009 12:15 ]

[curry684]

Woy schreef op donderdag 03 september 2009 @ 12:11:
Ik zie het probleem er ook niet direct in. Niet zozeer in GROUP_CONCAT op zichzelf, maar waarom zou je niet zelf Aggregate functions kunnen declareren ( Zou dat overigens ook niet in MS-SQL komen? Volgens mij is het in Oracle al mogelijk )

Dat kan in SQL Server ook gewoon: http://msdn.microsoft.com/en-us/library/ms182741.aspx

Geinig genoeg declareren ze in de example van die pagina de functie 'Concatenate' Het kan volgens mij ook zonder .NET overigens in native SQL.

In het eerste voorbeeld is ten minste duidelijk dat je niet zeker weet welke waarde SomeOtherField krijgt.

Even misverstandje rechtzetten: MySQL's random gedrag bij incomplete aggregating is niet echt random want repeatable, tis namelijk gebaseerd op de volgorde van fysieke storage, de 'clustered index' van SQL Server zeg maar. Als een nieuwe insert/delete echter tot page splits of page merges leidt kan dat dus wel de uitkomst wijzigen.

[ Voor 5% gewijzigd door curry684 op 03-09-2009 12:18 ]

[Woy]

curry684 schreef op donderdag 03 september 2009 @ 12:17:
[...]
Even misverstandje rechtzetten: MySQL's random gedrag bij incomplete aggregating is niet echt random want repeatable, tis namelijk gebaseerd op de volgorde van fysieke storage, de 'clustered index' van SQL Server zeg maar. Als een nieuwe insert/delete echter tot page splits of page merges leidt kan dat dus wel de uitkomst wijzigen.

Dat weet ik, maar IMHO kun je er beter vanuit gaan dat het random is, dan ervan uit gaan dat je altijd dezelfde terug krijgt ( Al lijkt het dan wel erg op de bekende RNG van Dilbert ). De storage volgorde hoeft namelijk niet perse afhankelijk te zijn van dat veld.

Dat is ook de reden dat ik niet expliciet zei dat je bij MySql random waardes terug krijgt

curry684 schreef op donderdag 03 september 2009 @ 12:17:
[...]
Dat kan in SQL Server ook gewoon: http://msdn.microsoft.com/en-us/library/ms182741.aspx

Dan had het ik het toch goed onthouden. Ik heb het zelf nog nooit echt nodig gehad ( Al zullen er vast situaties geweest zijn waar ik het ook met een custom aggregate function op had kunnen lossen ).

[ Voor 26% gewijzigd door Woy op 03-09-2009 12:30 ]

[curry684]

Woy schreef op donderdag 03 september 2009 @ 12:27:
[...]

Dat weet ik, maar IMHO kun je er beter vanuit gaan dat het random is, dan ervan uit gaan dat je altijd dezelfde terug krijgt ( Al lijkt het dan wel erg op de bekende RNG van Dilbert ). De storage volgorde hoeft namelijk niet perse afhankelijk te zijn van dat veld.

Dat is ook de reden dat ik niet expliciet zei dat je bij MySql random waardes terug krijgt

Je impliceerde dat een incomplete aggregate equivalent zou zijn aan een random() aggegrate functie, wat dus niet zo is - de correcte naam voor een equivalente aggegrate functie zou unpredictable() zijn

[Woy]

curry684 schreef op donderdag 03 september 2009 @ 12:31:
[...]
Je impliceerde dat een incomplete aggregate equivalent zou zijn aan een random() aggegrate functie, wat dus niet zo is - de correcte naam voor een equivalente aggegrate functie zou unpredictable() zijn

offtopic:
Je hebt gelijk, maar hoewel ik me er bewust van was, vond ik dit een leukere manier om het neer te zetten

[cariolive23]

curry684 schreef op donderdag 03 september 2009 @ 12:17:
Even misverstandje rechtzetten: MySQL's random gedrag bij incomplete aggregating is niet echt random want repeatable, tis namelijk gebaseerd op de volgorde van fysieke storage, de 'clustered index' van SQL Server zeg maar. Als een nieuwe insert/delete echter tot page splits of page merges leidt kan dat dus wel de uitkomst wijzigen.

En daar wil je dus niet van afhankelijk zijn, ik zie mezelf nog geen administratie o.i.d. bouwen die op page-niveau moet gaan controleren of resultaten wel correct zijn. Je gebruikt niet voor niets een DBMS, die hoort het opslaan en beheer van data eenvoudiger te maken. MySQL maakt het met dit soort gedrag juist moeilijker. En dus kostbaarder.

[RobIII]

curry684 schreef op donderdag 03 september 2009 @ 11:59:
[...]

Hmm in het kader leuke discussies moet ik zeggen dat ik GROUP_CONCAT een hele mooie nette constructie vind die ik graag ook in andere DBMS'en zou zien. Waarom zou het een ranziger aggregate function zijn dan max, min, stddev of count?

Nou; hier kan ik me wel in vinden. Punt is dat GROUP_CONCAT 99 v.d. 100 keer gebruikt wordt voor dingen die je feitelijk in je presentatielaag wil oplossen en met een GROUP_CONCAT werk je dus (IMHO) luie programmeurs in de hand. Een max/min/stddev of count is weer iets heel anders dan strings aan mekaar plakken (met als SEPARATOR '<br>' ) IMHO.

[Verwijderd]

RobIII schreef op donderdag 03 september 2009 @ 14:13:
[...]

Nou; hier kan ik me wel in vinden. Punt is dat GROUP_CONCAT 99 v.d. 100 keer gebruikt wordt voor dingen die je feitelijk in je presentatielaag wil oplossen en met een GROUP_CONCAT werk je dus (IMHO) luie programmeurs in de hand. Een max/min/stddev of count is weer iets heel anders dan strings aan mekaar plakken (met als SEPARATOR '<br>' ) IMHO.

Euh... da's wel ECHT lui.