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