запрос к трем таблицам

[sickboy]

запрос к трем таблицам

Привет.

Есть такой запрос:

SELECT music.id_mz as 'music.id_mz',
 music_album.id_mz as 'music_album.id_mz',
 music_album.id_ma as 'music_album.id_ma',
 music_tracks.id_ma as 'music_tracks.id_ma',
 music_tracks.id_mt as 'music_tracks.id_ma'
 FROM
 music inner join music_album
 ON
 music.id_mz=music_album.id_mz
 left join music_tracks
 ON
 music_album.id_ma=music_tracks.id_ma;

получаю:

+-------------+-------------------+-------------------+--------------------+--------------------+
| music.id_mz | music_album.id_mz | music_album.id_ma | music_tracks.id_ma | music_tracks.id_ma |
+-------------+-------------------+-------------------+--------------------+--------------------+
|           1 |                 1 |                 1 |                  1 |                  1 |
|           1 |                 1 |                 1 |                  1 |                  2 |
|           1 |                 1 |                 1 |                  1 |                  3 |
|           1 |                 1 |                 1 |                  1 |                  4 |
|           1 |                 1 |                 1 |                  1 |                  5 |
|           1 |                 1 |                 1 |                  1 |                  6 |
|           1 |                 1 |                 1 |                  1 |                  7 |
|           1 |                 1 |                 2 |                  2 |                  8 |
|           1 |                 1 |                 2 |                  2 |                  9 |
|           1 |                 1 |                 2 |                  2 |                 10 |
|           1 |                 1 |                 2 |                  2 |                 11 |
|           1 |                 1 |                 2 |                  2 |                 12 |
|           1 |                 1 |                 2 |                  2 |                 13 |
|           1 |                 1 |                 2 |                  2 |                 14 |
|           1 |                 1 |                 2 |                  2 |                 15 |
|           2 |                 2 |                 3 |                  3 |                 16 |
|           2 |                 2 |                 3 |                  3 |                 17 |
|           2 |                 2 |                 3 |                  3 |                 18 |
|           2 |                 2 |                 3 |                  3 |                 19 |
|           3 |                 3 |                 4 |                  4 |                 20 |
|           3 |                 3 |                 4 |                  4 |                 21 |
|           3 |                 3 |                 4 |                  4 |                 22 |
|           3 |                 3 |                 4 |                  4 |                 23 |
|           3 |                 3 |                 4 |                  4 |                 24 |
|           3 |                 3 |                 4 |                  4 |                 25 |
|           3 |                 3 |                 4 |                  4 |                 26 |
|           3 |                 3 |                 4 |                  4 |                 27 |
|           3 |                 3 |                 4 |                  4 |                 28 |
|           3 |                 3 |                 4 |                  4 |                 29 |
|           3 |                 3 |                 4 |                  4 |                 30 |
+-------------+-------------------+-------------------+--------------------+--------------------+

Из этого запроса нужно получить такой:

+-------------+--------------------------+-----------------------+
| music.id_mz | album_count            |tracks_count   |
+-------------+--------------------------+------------------------+
|           1 |                        2 |                      15|
|           2 |                        1 |                        4|
|           3 |                        1 |                      11|
+-------------+--------------------------+-----------------------+

 

[intelligent]

COUNT, GROUP BY

 

[sickboy]

угу, пробовал, но дальше вот этого не продвинулся:

SELECT
 music.id_mz as 'music.id_mz',
 COUNT(music_album.id_ma),
 COUNT(music_tracks.id_ma)
 FROM
 music inner join music_album
 ON
 music.id_mz=music_album.id_mz 
left join music_tracks
 ON
 music_album.id_ma=music_tracks.id_ma
GROUP BY music.artist;

-~{}~ 12.03.06 19:48:

+-------------+--------------------------+---------------------------+
| music.id_mz | COUNT(music_album.id_ma) | COUNT(music_tracks.id_ma) |
+-------------+--------------------------+---------------------------+
|           2 |                        4 |                         4 |
|           3 |                       11 |                        11 |
|           1 |                       15 |                        15 |
+-------------+--------------------------+---------------------------+

 

[Wicked]

DISTINCT

 

[sickboy]

Wicked
а можшь дать сразу готовый запрос, мне твоя подсказка к сожалению не помогла.

 

[chira]

SELECT
 music.id_mz as 'music.id_mz',
 COUNT([b]DISTINCT[/b] music_album.id_ma) AS album_count,
 COUNT(*) as tracks_count
 FROM
 music inner join music_album
 ON music.id_mz=music_album.id_mz 
left join music_tracks
 ON music_album.id_ma=music_tracks.id_ma
GROUP BY music.artist;

 

[sickboy]

спасибо.