MySQL Query Help

Discussion in 'Web Design and Development' started by ppc_michael, Jun 13, 2009.

  1. ppc_michael Guest

    ppc_michael

    Joined:
    Apr 26, 2005
    Location:
    Los Angeles, CA
    #1
    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. :(
     
  2. Mitthrawnuruodo Moderator emeritus

    Mitthrawnuruodo

    Joined:
    Mar 10, 2004
    Location:
    Bergen, Norway
    #2
    I haven't tried this, but see if this Google Answer is helpful:

    [Link]

    Looks a bit like the thing you're trying...
     
  3. ppc_michael thread starter Guest

    ppc_michael

    Joined:
    Apr 26, 2005
    Location:
    Los Angeles, CA
    #3
    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.:eek:
     
  4. hugo7 macrumors regular

    Joined:
    Oct 25, 2008
    #4
    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)

    Code:
    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
     
  5. ppc_michael thread starter Guest

    ppc_michael

    Joined:
    Apr 26, 2005
    Location:
    Los Angeles, CA

Share This Page