Page 1 of 1

[SOLVED] Get num rows of linked dbs that match criteria

Posted: Sat Nov 15, 2003 2:26 pm
by Cruzado_Mainfrm
i have two tables, one is called `artists`, which contains all the artists:
example:

Code: Select all

<lyrics>
  <!-- Table artists -->
    <artists>
        <artistid>7</artistid>
        <artistname>Sean Paul</artistname>
    </artists>
    <artists>
        <artistid>10</artistid>
        <artistname>Daddy Yankee</artistname>
    </artists>
</lyrics>
and another table that contains all the titles, called `titles` of course, linked to this one by the field artistid:

Code: Select all

<lyrics>
  <!-- Table titles -->
    <titles>
        <titleid>3</titleid>
        <artistid>1</artistid>
        <lyricid>1</lyricid>
        <titlename>Necesito De Tu Calor (Alberto Stylee)</titlename>
        <posterid>1</posterid>
    </titles>
    <titles>
        <titleid>15</titleid>
        <artistid>2</artistid>
        <lyricid>2</lyricid>
        <titlename>Salgo Filoteau</titlename>
        <posterid>1</posterid>
    </titles>
    <titles>
        <titleid>16</titleid>
        <artistid>7</artistid>
        <lyricid>4</lyricid>
        <titlename>La Tierra Ya Destruida</titlename>
        <posterid>1</posterid>
    </titles>
    <titles>
        <titleid>20</titleid>
        <artistid>7</artistid>
        <lyricid>8</lyricid>
        <titlename>Paradox</titlename>
        <posterid>1</posterid>
    </titles>
</lyrics>
and what i want to get is, the name of the artist, the artist id, the number of titles that the artist has and that's it.
i've been trying to do this so many times that i'm tired so i decided to post it here.

the result should look like:

Row Result #1:
array (
[artistid] = 14,
[artistname] = 'Some artist there',
[count(total of titles)] = X
)

where X is the number of titles that the artist has

i need to know how to do the sql code

EDIT: found the solution:

Code: Select all

SELECT a.artistid,a.artistname,count(t.titleid) FROM artists AS a LEFT JOIN titles AS t ON ( a.artistid = t.artistid ) GROUP BY artistname ORDER BY artistname

Posted: Sat Nov 15, 2003 4:05 pm
by Weirdan
BTW, GROUP BY <somefields> implies ORDER BY <somefields>. Keep your queries short :D

Posted: Sat Nov 15, 2003 6:01 pm
by Cruzado_Mainfrm
oh ok, i'll take that into account