MySQL Query Help

[ppc_michael]

I'm working on a site that lists music. I have two tables, one called music_artists and one called music_pieces.

music_artists has id and name. id is a unique number and name is the "user friendly" artist name.

music_pieces has id, artistid, title. id is a unique number, artistid corresponds to id in music_artists which says which artist wrote the piece, and title is the "user friendly" title.

I would like to generate an index that lists all the artists and the number of pieces they have in the database. So I would like it to look like this:

-Air (6)
-Hayden (4)
-Yann Tiersen (8)

etc etc.

Is there a way I can accomplish this with a single SQL query?

I suspect I need to JOIN and COUNT things, but I only have a basic SELECT/UPDATE/DELETE knowledge of MySQL.

 

[Mitthrawnuruodo]

I haven't tried this, but see if this Google Answer is helpful:

SELECT DISTINCT SalesPerson, Department From SalesTable I get

SalesPerson       Department
Eric Chun          AudioEq
Eric Chun          VideoEq
Eric Chun          Books
Fred Chin          Books
Fred Chin          Computers
Ricky Lal          Tools
Ricky Lal          VideoEq
Louis Marc         VideoEq
Louis Marc         AudioEq
Mandy Brewer       Computers
[...]
SELECT COUNT( DISTINCT Department ) As DepCount From SalesTable Group
By SalesPerson

SalesPerson       DepCount
Eric Chu           3
Fred Chin          2
Ricky Lal          2
Louis Marc         2
Mandy Brewer       1

[Link]

Looks a bit like the thing you're trying...

 

[ppc_michael]

Thank you, that looks a lot closer to what I need, but my problem is that the info is distributed across two tables, and that query only deals with one.

If no one knows, I guess I can just do it with multiple queries and PHP, but I'd rather not since that would take more processing time and be overly complicated.

 

[hugo7]

There are various ways to achieve this. One possibility is below (you can remove the text "union..." if you are not interested in "zero-song" artists)

select a.name, count(*)
from artists a, song s
where a.id = s.artistid
group by a.id
union
select a.name, 0
from artists a
where a.id not in (select artistid from songs)

In terms of grasping basic SQL, you might want to take a look at the following

http://en.wikibooks.org/wiki/Structured_Query_Language

 

[ppc_michael]

That's perfect! Thank you very much.