[SOLVED] help plz

[ccrevcypsys]

Heres the problem. I need to pull a list of albums with the album information. But i cant seem to word it right. So it is pulling out a list of all the songs like this

c how those are all the same album. I need that to only be one album listing so the onlything that would be on there is one row. how would i fix this??

	$productListQuery = "SELECT * FROM ".$glob['dbprefix']."StreamRush_albums a
					 LEFT JOIN ".$glob['dbprefix']."StreamRush_customer c
					 ON c.customer_id = a.artist_id
					 JOIN ".$glob['dbprefix']."StreamRush_inventory i
					 ON i.album_id = a.album_id
					 WHERE i.album_id = a.album_id ORDER BY a.album_id";

 

and here is my database table.

 

|------songs-------|        |------albums------|      |------customer-----|

    song_id                +----      album_id          +------  customer_id

    name                  |            artist_id--------+          other_info

    album_id    -------+

    other_info   

 

[Psycho]

So, does the price of $.99 apply to the album or is that the price for each title and needs to be added up?

[ccrevcypsys]

it needs to be added up... and the songs need to be counted...

[Psycho]

Hmmm, I dont see the "Songs" table listed in your query - is that the inventory table? Its kind of difficult for me to do this without the actual database to test against, but you could give this query a try:

 

SELECT *, SUM(i.price) AS album_price, COUNT(i.song_id) AS song_count

 

FROM ".$glob['dbprefix']."StreamRush_albums a

  LEFT JOIN ".$glob['dbprefix']."StreamRush_customer c

    ON c.customer_id = a.artist_id

  JOIN ".$glob['dbprefix']."StreamRush_inventory i

    ON i.album_id = a.album_id

 

WHERE i.album_id = a.album_id ORDER BY a.album_id

 

GROUP BY i.album_id

[ccrevcypsys]

yes its inventory.. sorry forgot to change that lol

[ccrevcypsys]

i get this error

 

MySQL Error Occured

n1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY i.album_id LIMIT 0, 10' at line 8

n

QUERY = SELECT *, SUM(i.p ice) AS album_p ice, COUNT(i.so g_id) AS so g_cou t FROM cc_St eamRush_albums a LEFT JOIN cc_St eamRush_custome c ON c.custome _id = a.a tist_id JOIN cc_St eamRush_i ve to y i ON i.album_id = a.album_id WHERE i.album_id = a.album_id ORDER BY a.album_id GROUP BY i.album_id

 

n

[ccrevcypsys]

IT WORKED I LOVE YOU LOL THANKS HAHAHAHAAHAHHAHA

now i can go rest