[SOLVED] Get num rows of linked dbs that match criteria
Posted: Sat Nov 15, 2003 2:26 pm
i have two tables, one is called `artists`, which contains all the artists:
example:
and another table that contains all the titles, called `titles` of course, linked to this one by the field artistid:
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:
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>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>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