[MySQL] Lastige query; dubbele JOIN

[Gersomvg]

Beste tweakers,

Ik ben een fotoalbum aan het maken wat voor het grootste deel af is. Als laatste ben ik een statistiekenpagina aan het maken waarop mensen kunnen zien wie de meeste foto's en reacties heeft geplaatst.
Voor wie de meeste reacties heeft geplaatst heb ik een redelijk eenvoudige query maar voor de fotorankinglijst is een wat ingewikkeldere nodig. Er is onder andere een JOIN nodig alleen krijg ik die tot mijn spijt niet goed. Het lastigste punt is dat een gebruiker meerdere albums kan hebben en dat je alle foto's uit die albums moet terugbrengen tot 1 gebruiker..

Dit is mijn db structuur (alleen de velden die betrekking hebben op het probleem):

Albums
* id
* member


Photos
* id
* album


Member
* id
* first_name


En zo wil ik het overzicht dus hebben:
1. Jack 215 photo's (in 3 albums)
2. Chris 123 photo's (in 1 album)
3. Laura 56 photo's (in 2 albums)

Ik kan ook een member-veld toevoegen aan de photos-tabel omdat ik de albums-tabel dan niet meer hoef aan te roepen. Dit wil ik alleen niet omdat je zo weinig mogelijk velden moet gebruiken in je db.
Is er iemand die hier een sql-query uit voort kan breien?

Ik had zoiets:
SELECT t1.first_name AS firstname,
COUNT(t2.id) AS photos,
COUNT(t3.id) AS albums
FROM members AS t1
JOIN photos AS t2, albums AS t3
ON (t3.member = t1.id AND t2.album = t3.id)

[ Voor 5% gewijzigd door Gersomvg op 15-08-2008 17:24 ]

[Noork]

Ik zou een join doen met beide tabellen, grouperen op member_id om het aantal foto's te bepalen, en vervolgens het aantal albums opahalen met een subselect. Misschien omslachtig, maar anders weet ik het ook niet.

Zoiets

SQL:

SELECT
   albums.id,
   albums.userid,
   member.id,
   member.naam,
   photos.id,
   photos.albumid,
   Count(member.id) as aantal_fotos,
   (SELECT count(id) from albums where albums.userid = member.id) as aantal_albums
FROM
   member
   Inner Join albums ON member.id = albums.userid
   Inner Join photos ON albums.id = photos.albumid
GROUP BY
   member.id

[ Voor 3% gewijzigd door Noork op 15-08-2008 18:39 ]

[Gersomvg]

Ik heb hem eventjes iets aangepast:

SQL:

SELECT
   albums.id,
   albums.member,
   members.id,
   members.first_name,
   members.mi,
   members.last_name,
   photos.album,
   COUNT(members.id) as aantal_fotos,
   (SELECT count(id) FROM albums WHERE albums.member = member.id) as aantal_albums
FROM
   members
INNER JOIN albums ON members.id = albums.member
INNER JOIN photos ON albums.id = photos.album
GROUP BY
   members.id

Er moet helaas nog ergens een fout zitten want bij mysql_fetch_array of num_rows krijg ik nog een error..

[Noork]

gersompie schreef op vrijdag 15 augustus 2008 @ 18:31:
Ik heb hem eventjes iets aangepast:

SQL:

SELECT...

Er moet helaas nog ergens een fout zitten want bij mysql_fetch_array of num_rows krijg ik nog een error..

Tja dat weet ik ook niet. Ik heb hier even 3 test tabellen aangemaakt, en deze query even snel in elkaar geknalt met Navicat. De query an sich werkt hier gewoon in MySql 5.0.45. Het kan natuurlijk ook aan de PHP code liggen. Probeer de query b.v. eens met Navicat of PHPMyAdmin.

Sowieso is het handig om je foutmeldingen te posten.

[Gersomvg]

Sorry.. ik wist niet dat je een query kon testen met phpmyadmin.. Er moest nog 1 member.id vervangen worden door members.id

Nu werkt tie Thanks..
Ik ga zeker even kijken naar dat Navicat

[pimlie]

Die subselect voor het aantal albums is eigenlijk overbodig, voor dit probleem heeft MySQL een COUNT( DISTINCT ...):

SQL:

SELECT  a.id, 
    a.userid, 
    m.id, 
    m.naam, 
    p.id, 
    p.albumid, 
    COUNT(p.id) AS aantal_fotos, 
    COUNT(DISTINCT a.id) AS aantal_albums 
FROM member AS m
INNER JOIN albums AS a ON a.userid = m.id 
INNER JOIN photos AS p ON p.albumid = a.id 
GROUP BY m.id

[Noork]

pimlie schreef op vrijdag 15 augustus 2008 @ 19:17:
Die subselect voor het aantal albums is eigenlijk overbodig, voor dit probleem heeft MySQL een COUNT( DISTINCT ...):

SQL:

SELECT ...
    COUNT(DISTINCT a.id) AS aantal_albums 
...

Mooi, wist niet dat je ook een distinct op een enkele kolom kon uitvoeren. Maar hier zorgt het er wel voor dat albums waar geen foto's in staan, niet worden meegenomen.

[pimlie]

Als je dat wilt dan doe je een LEFT JOIN op photos ipv een INNER...

[Verwijderd]

offtopic:
't Blijft een gruwel om te zien, al die niet geaggregeerde velden in een SELECT die niet in de GROUP BY voorkomen...

[ Voor 3% gewijzigd door Verwijderd op 15-08-2008 20:10 ]

[Gersomvg]

Noork schreef op vrijdag 15 augustus 2008 @ 19:42:
[...]

Mooi, wist niet dat je ook een distinct op een enkele kolom kon uitvoeren. Maar hier zorgt het er wel voor dat albums waar geen foto's in staan, niet worden meegenomen.

Die mogen wel worden meegenomen. De rankinglist is toch niet gesorteerd op albums maar op aantal foto's..

[drm]

Afterlife:

offtopic:
't Blijft een gruwel om te zien, al die niet geaggregeerde velden in een SELECT die niet in de GROUP BY voorkomen...

Is imho niet echt offtopic. Is namelijk foute SQL...

@TS: Zie hierover FAQ item over group by

[Cousin Boneless]

SQL:

select m.id, m.first_name, m.last_name, m.mi,
  ( select count(*)
    from albums a
    where a.member_id = m.id ) as album_count,
  ( select count(*)
    from photos p
    inner join albums a on a.id = p.album_id
    where a.member_id = m.id ) as photo_count
from members m

Ben niet zo gecharmeerd van het genereren van rijen om er vervolgens alleen een count uit te halen..

[SchizoDuckie]

Cousin Boneless schreef op zaterdag 16 augustus 2008 @ 12:46:

SQL:

select m.id, m.first_name, m.last_name, m.mi,
  ( select count(*)
    from albums a
    where a.member_id = m.id ) as album_count,
  ( select count(*)
    from photos p
    inner join albums a on a.id = p.album_id
    where a.member_id = m.id ) as photo_count
from members m

Ben niet zo gecharmeerd van het genereren van rijen om er vervolgens alleen een count uit te halen..

Maar een dubbele subselect mét join is dan minder ranzig?

[Cousin Boneless]

Het zijn drie datasets (naw gegevens, albums en foto's) die alleen door member_id worden verenigd. De hoofdquery is het selecteren van de gegevens van de member (immers is het aantal records in het resultaat overeenkomstig met dat van members). De counts hangen daar min of meer los aan. Ofwel, het is niet logisch om de naw gegevens van members te betrekken in het bepalen van de counts.

[ATS]

Overigens: als je het als een soort top-10 wil gaan gebruiken, dan lijkt het me wel handig om niet alle gegevens op te halen, maar alleen de top waar je daadwerkelijk in geïnteresseerd bent...