[SQL] Meervoudige count over gejoinde tabellen

[thomaske]

Inleiding
Er zijn 3 tabellen: tbl_theme, tbl_article en tbl_thm_connection. De laatste is een koppeltabel voor de eerste 2 (er is dus sprake van een 'veel-op-veel' relatie)

hier een vereenvoudigde (maar werkende) table-dump:

code:

CREATE TABLE `tbl_theme` (
    `thm_id` INT NOT NULL AUTO_INCREMENT ,
    `thm_name` VARCHAR( 50 ) NOT NULL ,
    PRIMARY KEY ( `thm_id` ) 
);

INSERT INTO `tbl_theme` (`thm_name` ) 
VALUES ('Thema 1'), ('Thema 2'), ('Thema 3'),('Thema 4'), ('Thema 5');

CREATE TABLE `tbl_article` (
    `art_id` INT NOT NULL AUTO_INCREMENT ,
    `art_title` VARCHAR( 50 ) NOT NULL ,
    `art_state` ENUM( 'A', 'B', 'C' ) NOT NULL ,
    PRIMARY KEY ( `art_id` ) 
);

INSERT INTO `tbl_article` (`art_title`, `art_state`) VALUES
('Artikel 1', 'A'), ('Artikel 2', 'A'), ('Artikel 3', 'A'),
('Artikel 4', 'A'), ('Artikel 5', 'A'), ('Artikel 6', 'B'),
('Artikel 7', 'B'), ('Artikel 8', 'C'), ('Artikel 9', 'C'),
('Artikel 10', 'C');

CREATE TABLE `tbl_thm_connection` (
    `con_thm_id` INT NOT NULL ,
    `con_art_id` INT NOT NULL ,
    PRIMARY KEY ( `con_thm_id` , `con_art_id` ) 
);

INSERT INTO `tbl_thm_connection` (`con_thm_id`, `con_art_id`) VALUES
(1,1),(1,6),(1,8),(1,9),(2,2),(2,7),(2,8),(3,3),(3,4),(3,5),
(4,6),(4,8),(4,10),(5,10),(5,1),(5,6);

Het resultaat dat ik zou willen is als volgt:

code:

------------------------------------------
| thm_name | state_a | state_b | state_c |
------------------------------------------
| Thema 1  |       1 |       1 |       2 |
| Thema 2  |       1 |       1 |       1 |
| Thema 3  |       3 |       0 |       0 |
| Thema 4  |       0 |       1 |       2 |
| Thema 5  |       1 |       1 |       1 |
------------------------------------------

Dus voor elk thema het aantal artikelen dat een bepaalde status heeft

De query die ik daarvoor in gedachte had is deze:

code:

SELECT
    thm_name
,   COUNT(art_s1.art_id) as articles_s1
,   COUNT(art_s2.art_id) as articles_s2
,   COUNT(art_s3.art_id) as articles_s3
FROM tbl_theme
LEFT JOIN tbl_thm_connection AS con_s1 ON con_s1.con_thm_id = thm_id
LEFT JOIN tbl_article AS art_s1 ON art_s1.art_id = con_s1.con_art_id AND art_s1.art_state = 'A'

LEFT JOIN tbl_thm_connection AS con_s2 ON con_s2.con_thm_id = thm_id
LEFT JOIN tbl_article AS art_s2 ON art_s2.art_id = con_s2.con_art_id AND art_s2.art_state = 'B'

LEFT JOIN tbl_thm_connection AS con_s3 ON con_s3.con_thm_id = thm_id
LEFT JOIN tbl_article AS art_s3 ON art_s3.art_id = con_s3.con_art_id AND art_s3.art_state = 'C'

GROUP BY thm_id

Alleen dit geeft niet het gewenste resultaat: afhankelijk van het aantal matches wordt de count meerdere keren geteld.

Heeft iemand enig idee hoe ik dit het beste kan aanpakken ?

Alvast bedankt!

(Database is MySQL 4.0.15, dus subqueries zijn niet mogelijk)
In werkelijkheid is de tabellenstructuur een stuk uitgebreider maar om het probleem te lokaliseren heb ik het tot de kern teruggebracht

[Verwijderd]

Kent MySQL ook "GROUP BY .... WITH ROLLUP" en/of "GROUP BY CUBE ..." ?
Hiermee is ( in MSSQL iig ) mogelijk om meerdere (sub)totalen te genereren met 1 statement. Miscchien dat je daar iets aan hebt ?

[thomaske]

Verwijderd schreef op donderdag 14 juli 2005 @ 12:49:
Kent MySQL ook "GROUP BY .... WITH ROLLUP" en/of "GROUP BY CUBE ..." ?
Hiermee is ( in MSSQL iig ) mogelijk om meerdere (sub)totalen te genereren met 1 statement. Miscchien dat je daar iets aan hebt ?

Had het inderdaad ookal in de manual gevonden, maar helaas:

code:

#1235 - This version of MySQL doesn't yet support 'ROLLUP'

[ Voor 19% gewijzigd door thomaske op 14-07-2005 13:00 ]

[marty]

zoiets?

MySQL:

SELECT
    CONCAT('Thema ',con_thm_id) AS thm_name,
    COUNT(ta1.art_id) AS state_a,
    COUNT(ta2.art_id) AS state_b,
    COUNT(ta3.art_id) AS state_c
#,  COUNT(*) AS total
FROM tbl_thm_connection AS ttc
    LEFT JOIN tbl_article AS ta1 ON ta1.art_id=ttc.con_art_id AND ta1.art_state='A'
    LEFT JOIN tbl_article AS ta2 ON ta2.art_id=ttc.con_art_id AND ta2.art_state='B'
    LEFT JOIN tbl_article AS ta3 ON ta3.art_id=ttc.con_art_id AND ta3.art_state='C'
GROUP BY con_thm_id

komt bij mij tenminste hetzelfde plaatje uit als jouw plaatje van hoe je wilt dat het er uit ziet

[ Voor 33% gewijzigd door marty op 14-07-2005 16:28 . Reden: nog even een CONCAT toegevoegd om het plaatje helemáál kloppend te maken :*) ]

[thomaske]

marty schreef op donderdag 14 juli 2005 @ 16:24:
zoiets?

[..]

Wow.. perfect! Ik zat gewoon veel te veel te joinen. Bedankt!

[lier]

marty schreef op donderdag 14 juli 2005 @ 16:24:
zoiets?

MySQL:

SELECT
    CONCAT('Thema ',con_thm_id) AS thm_name,
    COUNT(ta1.art_id) AS state_a,
    COUNT(ta2.art_id) AS state_b,
    COUNT(ta3.art_id) AS state_c
#,  COUNT(*) AS total
FROM tbl_thm_connection AS ttc
    LEFT JOIN tbl_article AS ta1 ON ta1.art_id=ttc.con_art_id AND ta1.art_state='A'
    LEFT JOIN tbl_article AS ta2 ON ta2.art_id=ttc.con_art_id AND ta2.art_state='B'
    LEFT JOIN tbl_article AS ta3 ON ta3.art_id=ttc.con_art_id AND ta3.art_state='C'
GROUP BY con_thm_id

komt bij mij tenminste hetzelfde plaatje uit als jouw plaatje van hoe je wilt dat het er uit ziet

Inderdaad, zolang je nog niet werkt met SQL 2005 zal je je in allemaal bochten moeten wringen om dit resultaat dynamisch te krijgen. Een statische oplossing zoals hierboven genoemd, is de meest eenvoudige.

[marty]

lier schreef op vrijdag 15 juli 2005 @ 09:29:
[...]


Inderdaad, zolang je nog niet werkt met SQL 2005 zal je je in allemaal bochten moeten wringen om dit resultaat dynamisch te krijgen. Een statische oplossing zoals hierboven genoemd, is de meest eenvoudige.

wat vind jij statisch aan die query dat je in SQL2005 dynamisch kan doen dan?