4 tabellen combineren en resultaat weergeven

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

4.546 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 21:06 gewijzigd op 14-12-2015 21:07

Brecht,

Geef de structuur van je tabellen eens door en wat voorbeeld data.
Dan zal ik het aan mijn kant eens nabouwen.

Bij voorkeur de sql statements, dat is het eenvoudigst voor mij. via phpMyAdmin Export makkelijk te maken.

Brecht S

14-12-2015 21:17

Kan ik jou de SQL files ergens doormailen of zo? Is dat niet het gemakkelijkste?

Jan de Laet

14-12-2015 21:19

Je kunt PM sturen via mijn profiel. Maar is er iets bij dat niet gedeeld kan worden?

Brecht S

14-12-2015 21:22

Ik heb momenteel 3 SQL files en kan jou die doorsturen, alleen niet via PM want kan daar geen files uploaden. Ik zet liever de inhoud ervan niet hier open en bloot. Zijn data van leads, prospect en klanten.

[size=xsmall]Toevoeging op 14/12/2015 21:26:13:[/size]

Ik heb de inhoud even gekopieëerd en geplakt in een pm daarnet (3 pm's)

Jan de Laet

14-12-2015 22:16 gewijzigd op 14-12-2015 22:17

Volgens mij klopt deze voor acedemy key 11:


SELECT 
    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
FROM academy
 JOIN (SELECT DISTINCT cont_status FROM contacten) AS c

 JOIN 
    (SELECT academy_id, cont_status, 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
WHERE id= 11 
GROUP BY id, titel, bron

Er is een kleine wijziging de LEFT OUTER JOIN na de subquery is een JOIN geworden.

Dit geeft op basis van jouw data dit resultaat voor id 11:


Id Titel   Bron     A Ko L Pr Kl Po     (Anoniem, Klant, Lead, Prospect, Klant, Pool
11 Titel11          0  0 0  0  1  0 
11 Titel11 facebook 2  0 0  0  2  0 
11 Titel11 website  1  0 0  0  0  0

En die getallen kloppen met je gegevens.

Brecht S

14-12-2015 22:21 gewijzigd op 14-12-2015 22:23

Ik moet wel nog even controleren maar inderdaad zijn de dubbele records er nu wel uit.
Alleen heb ik geen bron bij al hetgeen via de website is binnengekomen (en dus niet van een externe link zoals facebook bvb). Kan ik op de een of andere manier diegene die geen bron vermelding hebben op website zetten via de query? Of moet ik mijn scripten aanpassen dat er toch altijd een bron is (indien geen opgegeven moet dit automatisch 'website' worden)?

[size=xsmall]Toevoeging op 14/12/2015 22:31:29:[/size]

En als ik in principe hetzelfde wil doen met de download kant ook dan moet ik waarschijnlijk terug een subquery in een join ertussen steken en een paar SUM's toevoegen?

Jan de Laet

14-12-2015 22:31

Het is natuurlijk beter om te zorgen dat je altijd bron hebt.
Maar eventueel kan CASE je hier helpen.


SELECT 
    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
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
WHERE id= 11 
GROUP BY id, titel, bron

Ik vervang hier een lege bron door 'website' in de subquery

Brecht S

14-12-2015 22:33

En als ik in principe hetzelfde wil doen met de download kant ook dan moet ik waarschijnlijk terug een subquery in een join ertussen steken en een paar SUM's toevoegen?

Jan de Laet

14-12-2015 22:42

Voor de downloads had je toch geen bron?

Dus voor zover ik het nog begrijp, heb je de query voor de landing_view plus download totalen per id. Die toont dus 2 x 6 totalen
Binnen de lus waarin je deze toont, heb je telkens een 2e query voor de bron vermelding van landing_views. De resultaten hiervan kun je tonen op vervolgregels in je html tabel, nog eens 6 totalen per bron 1 regel.

Brecht S

14-12-2015 22:47 gewijzigd op 14-12-2015 22:52

De downloads hebben ook een bron. En die is altijd dezelfde als de landingview. Bvb: iemand is doorgestuurd van facebook: 1 landingview met bron facebook en daarna eventueel (als die het wil downloaden) ook een download met bron facebook.

Kan ik dus een nieuwe subquery JOINen de tabel downloads? Zoals we op de eerst besproken (in het begin van het topic) query hadden, op dezelfde manier?

[size=xsmall]Toevoeging op 14/12/2015 22:50:06:[/size]

Zoiets dus:


SELECT 
    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
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 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	 
	 

	 
WHERE id= '$academy_id' 
GROUP BY id, titel, bron

Alleen heb ik hier een foutmelding 'Column 'bron' in field list is ambiguous'

Reageren

Inloggen om te reageren