Query met meerdere tafels
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT DN.*,
(SELECT COUNT(*)
FROM site_pages SP, site_widgets SW
WHERE SP.dashboard_id = DN.dashboard_id
AND SP.isActive = 1
AND SW.dashboard_id = DN.dashboard_id
AND SW.isActive = 1
) AS counter
FROM dashboard_navigation DN
ORDER BY DN.dashboard_sequence
(SELECT COUNT(*)
FROM site_pages SP, site_widgets SW
WHERE SP.dashboard_id = DN.dashboard_id
AND SP.isActive = 1
AND SW.dashboard_id = DN.dashboard_id
AND SW.isActive = 1
) AS counter
FROM dashboard_navigation DN
ORDER BY DN.dashboard_sequence
De query geeft geen fouten alleen counter voor beide tafels staat om 0. Hetr heeft duidelijk iets te maken met de aliassen ik weet alleen niet wat.
Toevoeging op 17/04/2018 11:37:46:
Ik heb de oplossing gevonden. Voor een ieder die dit wellicht ook nodig heeft. Dit is de uiteindelijke query:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT DN.*,
(SELECT COUNT(*)
FROM site_pages SP
WHERE SP.dashboard_id = DN.dashboard_id
AND isActive = 1)+
(SELECT COUNT(*)
FROM site_widgets SW
WHERE SW.dashboard_id = DN.dashboard_id
AND isActive = 1)+
(SELECT COUNT(*)
FROM blog_posts BP
WHERE BP.dashboard_id = DN.dashboard_id) AS counters
FROM dashboard_navigation DN
(SELECT COUNT(*)
FROM site_pages SP
WHERE SP.dashboard_id = DN.dashboard_id
AND isActive = 1)+
(SELECT COUNT(*)
FROM site_widgets SW
WHERE SW.dashboard_id = DN.dashboard_id
AND isActive = 1)+
(SELECT COUNT(*)
FROM blog_posts BP
WHERE BP.dashboard_id = DN.dashboard_id) AS counters
FROM dashboard_navigation DN
Er zijn nog geen reacties op dit bericht.