4 tabellen combineren en resultaat weergeven

Door Brecht S op 12-12-2015 11:25 gewijzigd op 12-12-2015 14:01

4.545 views

Ik ben al een tijdje aan het zoeken naar de juiste query maar geraak er niet aan uit. Hieronder een greep uit mijn 4 mysql tabellen die ik wil combineren:

Tabel academy_landingviews


id  user_id  academy_id

Tabel academy_download


id  user_id  academy_id

Tabel academy


id  titel

Tabel contacten


id  status

Nu is het zo dat ik een overzicht wil van alle ebooks die geplaatst zijn onder de tabel academy in combinatie met de status bij de contacten. De status kan bvb koud, lead, prospect of klant zijn.
Wat ik tot nu toe heb zitten proberen:


SELECT 
	a.titel, alv.academy_id, SUM(alv.view) as alvv, COUNT(ad.id) as adid
FROM 
	academy_landingviews alv
INNER JOIN
	academy_download ad
ON
	alv.academy_id = ad.academy_id
INNER JOIN
	academy a
ON
	alv.academy_id = a.id
INNER JOIN
	contacten c
ON
	alv.user_id = c.id
GROUP BY 
        alv.academy_id

Ik krijg nu een overzicht van alle resultaten grouped by de titel van een ebook. Maar nog niet in combinatie met de contacten status. Verder dan dit geraak ik niet. Iemand een suggestie?
Misschien is mijn query ook deels verkeerd. Geen idee...
Nu maak ik een overzicht in een html tabel (zie hieronder) met de resultaten (tabel zit in een loop). Later wil ik dit in grafieken steken.

HTML tabel:


<p>Cijfers downloads/landingviews ebooks/wp met ID = <? echo $row['academy_id']; ?> en titel = <?php echo $row['titel']; ?></p>
<table width="50%">
  <tr>
    <td>Anonieme views</td>
    <td><?php echo $row['alvv']; ?></td>
  </tr>
  <tr>
    <td>Anonieme downloads</td>
    <td><?php echo $row['adid']; ?></td>
  </tr>
  <tr>
    <td>Totale conversie</td>
    <td><?php echo round((($row['adid'] / $row['alvv']) * 100), 0); ?>%</td>
  </tr>
</table>

Jan de Laet

14-12-2015 22:54

Waarschijnlijk kun je dan dit toevoegen als JOIN


 JOIN 
    (SELECT academy_id, cont_status, 
	 case when bron='' then 'website' else bron end as bron, 
	 count(*) as aantal
     FROM academy_download ad
     LEFT OUTER JOIN contacten c ON c.id=ad.user_id
     GROUP BY academy_id, cont_status, bron) AS dl
   ON academy.id=dl.academy_id and c.cont_status=dl.cont_status

en in de SELECT de SUM regels herhalen voor de dl velden

Brecht S

14-12-2015 23:08 gewijzigd op 14-12-2015 23:15

En, ik weet niet of het juist is, nu zijn de cijfers met onderstaande query verkeerd bij de downloads en precies ook niet altijd juist bij de landingviews.
Ik heb het 'bron' kolom probleem (zie mysql error) weggewerkt zoals je kan zien hieronder, maar dat zal waarschijnlijk ook niet echt juist zijn aangezien de cijfers nu niet meer juist zijn:


    id, titel, bron,
    SUM(CASE WHEN (lv.cont_status='anoniem')  THEN lv.aantal ELSE 0 END) AS SumLvAnoniem1,
    SUM(CASE WHEN (lv.cont_status='koud')     THEN lv.aantal ELSE 0 END) AS SumLvKoud1,
    SUM(CASE WHEN (lv.cont_status='lead')     THEN lv.aantal ELSE 0 END) AS SumLvLead1,
    SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect1,
    SUM(CASE WHEN (lv.cont_status='klant')    THEN lv.aantal ELSE 0 END) AS SumLvKlant1,
    SUM(CASE WHEN (lv.cont_status='pool')     THEN lv.aantal ELSE 0 END) AS SumLvPool1,
	
    SUM(CASE WHEN (dl.cont_status='anoniem')  THEN dl.aantal ELSE 0 END) AS SumDlAnoniem1,
    SUM(CASE WHEN (dl.cont_status='koud')     THEN dl.aantal ELSE 0 END) AS SumDlKoud1,
    SUM(CASE WHEN (dl.cont_status='lead')     THEN dl.aantal ELSE 0 END) AS SumDlLead1,
    SUM(CASE WHEN (dl.cont_status='prospect') THEN dl.aantal ELSE 0 END) AS SumDlProspect1,
    SUM(CASE WHEN (dl.cont_status='klant')    THEN dl.aantal ELSE 0 END) AS SumDlKlant1,
    SUM(CASE WHEN (dl.cont_status='pool')     THEN dl.aantal ELSE 0 END) AS SumDlPool1
FROM academy
 JOIN (SELECT DISTINCT cont_status FROM contacten) AS c

	 
	 
 JOIN 
    (SELECT academy_id, cont_status,
	 (case when bron='' then 'website' else bron end) as bron, count(*) as aantal
     FROM academy_landingviews alv
     LEFT OUTER JOIN contacten c ON c.id=alv.user_id
     GROUP BY academy_id, cont_status, bron) AS lv
   ON academy.id=lv.academy_id and c.cont_status=lv.cont_status

 JOIN
   (SELECT academy_id, cont_status, 
	 (case when bron='' then 'website' else bron end) as bron2, count(*) as aantal
     FROM academy_download adl
     JOIN contacten c ON c.id=adl.user_id
     GROUP BY academy_id, cont_status, bron2) AS dl
   ON academy.id=dl.academy_id and c.cont_status=dl.cont_status	 
	 
	 
	 
	 
	 
	 
WHERE id= '$academy_id' 
GROUP BY id, titel, bron

En de bijhorende html tabel:


  <tr>
    <td><?php echo $row['id']; ?> - Landingviews door anoniem via <?php echo $row['bron']; ?></td>
    <td><?php echo $row['SumLvAnoniem1']; ?></td>
    <td>(<?php echo $row['id']; ?>)Downloads door anoniem via <?php echo $row['bron']; ?></td>
    <td><?php echo $row['SumDlAnoniem1']; ?></td>
  </tr>
  <tr>
    <td><?php echo $row['id']; ?> - Landingviews door koud via <?php echo $row['bron']; ?></td>
    <td><?php echo $row['SumLvKoud1']; ?></td>
    <td>(<?php echo $row['id']; ?>)Downloads door koud via <?php echo $row['bron']; ?></td>
    <td><?php echo $row['SumDlKoud1']; ?></td>
  </tr>
  <tr>
    <td><?php echo $row['id']; ?> - Landingviews door prospect via <?php echo $row['bron']; ?></td>
    <td><?php echo $row['SumLvProspect1']; ?></td>
    <td>(<?php echo $row['id']; ?>)Downloads door prospect via <?php echo $row['bron']; ?></td>
    <td><?php echo $row['SumDlProspect1']; ?></td>
  </tr>
  <tr>
    <td><?php echo $row['id']; ?> - Landingviews door klant via <?php echo $row['bron']; ?></td>
    <td><?php echo $row['SumLvKlant1']; ?></td>
    <td>(<?php echo $row['id']; ?>)Downloads door klant via <?php echo $row['bron']; ?></td>
    <td><?php echo $row['SumDlKlant1']; ?></td>
  </tr>
  <tr>
    <td><?php echo $row['id']; ?> - Landingviews door lead via <?php echo $row['bron']; ?></td>
    <td><?php echo $row['SumLvLead1']; ?></td>
    <td>(<?php echo $row['id']; ?>)Downloads door lead via <?php echo $row['bron']; ?></td>
    <td><?php echo $row['SumDlLead1']; ?></td>
  </tr>
  <tr>
    <td><?php echo $row['id']; ?> - Landingviews door pool via <?php echo $row['bron']; ?></td>
    <td><?php echo $row['SumLvPool1']; ?></td>
    <td>(<?php echo $row['id']; ?>)Downloads door pool via <?php echo $row['bron']; ?></td>
    <td><?php echo $row['SumDlPool1']; ?></td>
  </tr>

Jan de Laet

15-12-2015 16:54

Dit misschien


SELECT
	id, titel, b.bron,
    SUM(CASE WHEN (lv.cont_status='anoniem')  THEN lv.aantal ELSE 0 END) AS SumLvAnoniem1,
    SUM(CASE WHEN (lv.cont_status='koud')     THEN lv.aantal ELSE 0 END) AS SumLvKoud1,
    SUM(CASE WHEN (lv.cont_status='lead')     THEN lv.aantal ELSE 0 END) AS SumLvLead1,
    SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect1,
    SUM(CASE WHEN (lv.cont_status='klant')    THEN lv.aantal ELSE 0 END) AS SumLvKlant1,
    SUM(CASE WHEN (lv.cont_status='pool')     THEN lv.aantal ELSE 0 END) AS SumLvPool1,
    
    SUM(CASE WHEN (dl.cont_status='anoniem')  THEN dl.aantal ELSE 0 END) AS SumDlAnoniem1,
    SUM(CASE WHEN (dl.cont_status='koud')     THEN dl.aantal ELSE 0 END) AS SumDlKoud1,
    SUM(CASE WHEN (dl.cont_status='lead')     THEN dl.aantal ELSE 0 END) AS SumDlLead1,
    SUM(CASE WHEN (dl.cont_status='prospect') THEN dl.aantal ELSE 0 END) AS SumDlProspect1,
    SUM(CASE WHEN (dl.cont_status='klant')    THEN dl.aantal ELSE 0 END) AS SumDlKlant1,
    SUM(CASE WHEN (dl.cont_status='pool')     THEN dl.aantal ELSE 0 END) AS SumDlPool1
FROM academy
 JOIN (SELECT DISTINCT cont_status FROM contacten) AS c
 JOIN (SELECT DISTINCT (case when bron='' then 'website' else bron end) as bron FROM academy_landingviews) AS b
     
     
 LEFT OUTER JOIN 
    (SELECT academy_id, cont_status,
     (case when bron='' then 'website' else bron end) as bron, count(*) as aantal
     FROM academy_landingviews alv
     LEFT OUTER JOIN contacten c ON c.id=alv.user_id
     GROUP BY academy_id, cont_status, bron) AS lv
   ON academy.id=lv.academy_id and c.cont_status=lv.cont_status and b.bron=lv.bron

 LEFT OUTER JOIN
   (SELECT academy_id, cont_status, 
     (case when bron='' then 'website' else bron end) as bron, count(*) as aantal
     FROM academy_download adl
     JOIN contacten c ON c.id=adl.user_id
     GROUP BY academy_id, cont_status, bron) AS dl
   ON academy.id=dl.academy_id and c.cont_status=dl.cont_status and b.bron=dl.bron
     
WHERE id= 11 
GROUP BY id, titel, b.bron

Wat doe ik nu:
1. Ik maak een cross join tussen de titels en alle voorkomende status en bron SELECT plus 2 JOIN
2. Ik maak een subquery voor aantallen landing_views per titel status en bron
3. Ik maak een subquery voor aantallen downloads ook per titel status en bron
4. Deze voeg ik samen, en groepeer ze.

Ik raad je wel aan om 2 nieuwe tabellen te maken (mocht je die nog niet hebben) voor de toegestane waardes van status en van de bron.
Dan kan je de 2 JOINs vervangen door:


 JOIN (SELECT cont_status FROM contact_values) AS c
 JOIN (SELECT bron FROM bron_values) AS b

Is wat mij betreft veel beter en ook wat netter.

Brecht S

21-12-2015 15:54 gewijzigd op 21-12-2015 15:55

@Jan: op het eerste zicht lijkt dit ok nu. Ik doe nog een aantal testen om te zien of de cijfers daadwerkelijk ook juist zijn. Bedankt tot nu toe om me te helpen.
Die aparte tabellen die je voorstelde heb ik niet. Maar ik wil het wel aanpassen zodat ik ze wel heb.

Reageren

Inloggen om te reageren