query help

[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 i.*,a.*,c.*  FROM ".$glob['dbprefix']."albums a
					 LEFT JOIN ".$glob['dbprefix']."customer c
					 ON c.customer_id = a.artist_id
					 JOIN ".$glob['dbprefix']."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   

 

[fenway]

why the left join then the inner join? what do you mean "one row"?

[ccrevcypsys]

how would i code that?

im not the best with mysql

[fenway]

I don't understand how you decided on that query.

[ccrevcypsys]

well i dont know i just tried things and that one was the closest. But not the one i wanted. How should i have made that query?

[fenway]

why is the customer table in there at all? don't you just want songs/albums?

[ccrevcypsys]

yes but the customer table has the customer/artists information for their names.

 

and i just want to make this the album listings i already have the song one.

[fenway]

Then you should use inner join for each step, assuming every link is always present.  Drop the where clause, since it's not doing anything.  And don't select * unless you actually need to.

[ccrevcypsys]

ok well then i have made it like this

$productListQuery = "SELECT 
					 a.artist_id, a.album_id,
					 i.image, i.albumName, i.album_id,
					 c.screenname, c.customer_id
					 FROM ".$glob['dbprefix']."StreamRush_albums a
					 INNER JOIN ".$glob['dbprefix']."StreamRush_customer c
					 INNER JOIN ".$glob['dbprefix']."StreamRush_inventory i
					 ORDER BY a.album_id";

 

and now it returns with 281 pages of the same information...

[fenway]

Yes, because you've left out the ON clauses for each JOIN...

 

ON c.customer_id = a.artist_id

 

ON i.album_id = a.album_id

 

This is a double-post