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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Cruzado_Mainfrm
Forum Contributor
Posts: 346
Joined: Sun Jun 15, 2003 11:22 pm
Location: Miami, FL

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

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

BTW, GROUP BY <somefields> implies ORDER BY <somefields>. Keep your queries short :D
Cruzado_Mainfrm
Forum Contributor
Posts: 346
Joined: Sun Jun 15, 2003 11:22 pm
Location: Miami, FL

Post by Cruzado_Mainfrm »

oh ok, i'll take that into account
Post Reply