[MSSQL] Probleem met joins en distincts

[Giblet]

Ik ben nu al enkele uren aan het stoeien met een query en ik kom er echt niet uit. Zowel op google als hier kon ik het antwoord niet echt vinden, terwijl het me niet zo heel moeilijk lijkt.

De situatie is als volgt:

Ik heb 2 tabellen:

code:

Fotos
---------------------------------
FotoID         int
FotoUrl        varchar(150)
FotoObjID      smallint
FotoStart      bit

en

code:

Objecten
---------------------------------
ObjID        smallint
ObjAdres     varchar(100)

In de tweede tabel staan nog wel wat velden, maar die doen niet ter zake.

Ik wil graag een lijstje krijgen waarin van ieder ObjID met 1 FotoUrl verschijnt.
Wanneer ik deze query uitvoer:

code:

SELECT     Objecten.ObjID, Fotos.FotoUrl
FROM         Objecten LEFT OUTER JOIN
                      Fotos ON Objecten.ObjID = Fotos.FotoObjID

Krijg ik een overzicht van alle objecten maar de objecten welke meerdere foto's hebben komen dan ook meerdere malen voor.
Het is de bedoeling dat alle objecten getoond worden (of ze nu foto's bevatten of niet), en die objecten die foto's bevatten moeten alleen de eerste foto tonen (waarbij Fotos.FotoStart dus true is).
Bij de objecten die dan geen foto's bevatten moet een plaatje komen dat er geen foto's beschikbaar zijn (in de FotoUrl kolom een nopicture.jpg waarde o.i.d.)

Als ik deze query probeer:

code:

SELECT     Objecten.ObjID, Fotos.FotoUrl
FROM         Objecten LEFT OUTER JOIN
                      Fotos ON Objecten.ObjID = Fotos.FotoObjID
WHERE     (Fotos.FotoStart = 1)

Krijg ik alleen de objecten terug die wel een foto bevatten.

Hoe ik ook bezig ga met joins en distincts, ik kom er niet uit.
Kunnen jullie me een duwtje in de goede richting geven?

P.s. het gaat over MS SQL Server 2000

[ Voor 3% gewijzigd door Giblet op 05-01-2006 17:35 ]

[majornono]

Zoiets?

code:

SELECT Objecten.ObjID, MAX(Fotos.FotoUrl)
FROM Objecten
LEFT JOIN Fotos
ON Objecten.ObjID = Fotos.FotoObjID
AND Fotos.Fotostart = 1
GROUP BY Objecten.ObjID

[P_de_B]

Even voor de toekomst:

Hoe werken joins nu eigenlijk
SELECT DISTINCT
Hoe werkt dat group by nu eigenlijk

[Verwijderd]

Het is laat en in het kader dat ik morgen toch niets te doen heb (not) zat ik naar de query te kijken en het leek mij wat overdreven gekunseld

SQL:

SELECT Objecten.ObjID, Fotos.FotoURL
FROM Objecten LEFT JOIN Fotos ON ( Objecten.ObjID=Fotos.FotoOBJID AND Fotos.FotoStart=1)

Lijkt mij genoeg te moeten zijn.

Edit:

Hum, er moet ook nog een alternatieve waarde worden ingevuld.
Met het CASE statement moet dat ook lukken
Iets als

SQL:

SELECT Objecten.ObjID,CASE Fotos.FotoURL 
  WHEN NULL THEN "nopicture.jpg"
  ELSE  Fotos.FotoURL
  END AS AdaptedFotoURL
FROM Objecten LEFT JOIN Fotos ON ( Objecten.ObjID=Fotos.FotoOBJID AND Fotos.FotoStart=1)

Dit is natuurlijk niet getest aangezien ik hier thuis geen SQL Server bij de hand heb.

[ Voor 43% gewijzigd door Verwijderd op 06-01-2006 02:55 ]

[majornono]

Jan Klaasen, Topic starter wil graag per record uit de Objecten tabel 1 resultaat terug. Er kunnen meerdere records uit fotos gekoppeld zijn.

In jouw query komen er meerdere records terug.

[Verwijderd]

majornono schreef op vrijdag 06 januari 2006 @ 07:43:
Jan Klaasen, Topic starter wil graag per record uit de Objecten tabel 1 resultaat terug. Er kunnen meerdere records uit fotos gekoppeld zijn.

In jouw query komen er meerdere records terug.

Ik dacht al dat je dat ging zeggen

Op zich heb je gelijk. Echter, in mijn ogen kan (en mag) er slechts 1 startfoto zijn. Mijn query retourneert enkel 2 waarden als die regel gebroken wordt. De vraag is of dat erg is. Jou query heeft namelijk twee nadelen. 1) Het is trager (max en group by) en 2) De geretourneerde waarde van de fotoURL is onvoorspelbaar. Het is één van de records met een startfoto maar welke?

Aangezien het in mijn ogen ongewenst is dat er meer dan 1 foto als startfoto gedefinieerd is, "verbergt" jou query ook nog eens een bug in het systeem van de TS. Gebruikers die een bepaalde foto als startfoto aanvinken verwachten dat die foto als startfoto terugkomt. Dat is bij jou echter niet altijd het geval.

Dit alles wordt in mijn ogen veroorzaakt door een fout in het databaseontwerp. Startfoto zou in mijn ogen een attribuut moeten zijn in de objectentabel (of desnoods een attribuut in een tabel met een 1 op 1 relatie met die objectentabel) en niet een attribuut in de foto tabel.

Ook met dit ontwerp valt het natuurlijk af te dwingen dat er maar 1 startfoto gedefinieerd kan zijn. Zowel met mijn query als jou query is dat eigenlijk noodzakelijk voor een correcte en voorspelbare werking van het systeem. Maar in dat geval dan verdwijnt het voordeel van jou query.

[ Voor 11% gewijzigd door Verwijderd op 06-01-2006 11:35 ]

[majornono]

Verwijderd schreef op vrijdag 06 januari 2006 @ 11:32:

Dit alles wordt in mijn ogen veroorzaakt door een fout in het databaseontwerp. Startfoto zou in mijn ogen een attribuut moeten zijn in de objectentabel (of desnoods een attribuut in een tabel met een 1 op 1 relatie met die objectentabel) en niet een attribuut in de foto tabel.

Ook met dit ontwerp valt het natuurlijk af te dwingen dat er maar 1 startfoto gedefinieerd kan zijn. Zowel met mijn query als jou query is dat eigenlijk noodzakelijk voor een correcte en voorspelbare werking van het systeem. Maar in dat geval dan verdwijnt het voordeel van jou query.

Helemaal mee eens. Startfoto moet m.i. ook een attribuut zijn in de fototabel.
Mijn query was idd. niet waterdicht en waarschijnlijk langzaam. Maar voldeed wel aan de informatiebehoefte.

[Giblet]

Nuttige info allemaal. Bedankt voor de replies.
Heb het als volgt opgelost (met wat extra velden die ook nodig waren)

code:

SELECT     Objecten.ObjID, Objecten.ObjAdres, Types.TypeNaam, Objecten.ObjVraagPrijs, CASE WHEN MAX(Fotos.FotoUrl) IS NULL 
                      THEN 'nopic.jpg' ELSE Fotos.FotoUrl END AS FotoUrl
FROM         Objecten LEFT OUTER JOIN
                      Fotos ON Objecten.ObjID = Fotos.FotoObjID AND Fotos.FotoStart = 1 LEFT OUTER JOIN
                      Types ON Objecten.ObjTypeID = Types.TypeID
WHERE     Objecten.ObjSoortID = @SoortID AND Objecten.ObjPlaatsID = @PlaatsID AND ObjZichtbaar=1 AND ObjKoopHuur = @KoopHuur
GROUP BY Objecten.ObjID, Objecten.ObjAdres, Types.TypeNaam, Fotos.FotoUrl, ObjVraagPrijs
ORDER BY ObjAdres

Dit werkte voor mij.

Even een kort vraagje nog. Ik lees hier in veel topics dat queries niet optimaal zijn qua performance. Deze is dat ongetwijfeld ook niet.
Ik snap de argumenten om het inderdaad goed aan te leren, maar is er echt een merkbaar verschil bij kleine applicaties (in mijn geval een kleine makelaarssite)??

[Verwijderd]

Performance is altijd een issue. Ook in kleine databases aangezien die regelmatig de neiging hebben te groeien. En op zo'n moment is het meestal niet handig om de applicatie te herschrijven. Dus het is altijd handig om over performance na te denken.

Dat betekent echter niet dat je per definitie alles met stored procedures moet doen en altijd alle performance uit elke query moet persen. Wél dat je moet voorkomen dat je echt rare queries maakt en in ieder geval bewust goede indexen moet zetten (en dus niet elke kolom blindelings gaan indexeren).

Echt optimaliseren is een kunst en sterk afhankelijk van welke database je gebruikt, de versie van die database en zelfs de specifieke toepassing. Gelukkig kun je, met een beetje ervaring, vaak snel zien of een query enigszins effectief is. Komt de query onlogisch over dan is die dat vaak ook . Punt is dan alleen nog hoe je de query moet verbeteren. Dat is vaak lastiger en soms leidt dat weer tot inzichten die aanpassingen in het datamodel vereisen.

In dit geval komt een Max en een Group by mij overdreven over voor de eenvoudige vraag "geef mij de startfoto die bij een object hoort." Elk object heeft 1 (of geen) startfoto. Dit is een 1 op 1 relatie en dus zou een inner join genoeg moeten zijn. Dit "inzicht" suggereert dan ook dat een databaseontwerp wat meerdere startfotos toestaat niet correct is. (Zeker niet als je dan er ook van eist dat je maar 1 rij per object mag terugkrijgen) Maar goed, dat is een andere discussie.

[Giblet]

Mja, zoals je al eerder zij zou het database ontwerp aangepast moeten worden.

Dit alles wordt in mijn ogen veroorzaakt door een fout in het databaseontwerp. Startfoto zou in mijn ogen een attribuut moeten zijn in de objectentabel (of desnoods een attribuut in een tabel met een 1 op 1 relatie met die objectentabel) en niet een attribuut in de foto tabel.

Ik denk dat ik dat maar ga doen dan