Re[2]: [PHP-DB] Selecting twice from the same table

Hello Justin, Thursday, September 13, 2001, 11:07:26 AM, you wrote: JB> On Thu, 13 Sep 2001, Jacob Singh wrote: >> Hello, >> main >> ---------- >> ID autonumber >> content_ID enum('artist','song','album',user >> function','genre', etc.) >> name char >> img char >> des char >> etc. etc. >> >> songs >> ----------- >> main_ID ( link to main table) int >> artist_ID (link to parent) int >> length (length of song) char >> size (size of file) char >> file (file location) char >> etc. >> etc. >> >> Now my goal is to take a given number of songs say 10 songs of >> various artists and get a list like: >> >> artist 1 >> song1 - length - size >> song2 - length - size >> song3 - length - size >> artist 2 >> song1 - length - size >> song2 - length - size >> song3 - length - size JB> I -think- I'm seeing your database design... but I'm not sure if your JB> parent id (artist_id) links back to the songs table or the main table.. at JB> any rate, a simple GROUP BY on your query would let you query out songs by JB> criteria and then sort them by artist. JB> BTW, you may wish to take a look at any similar projects at JB> www.freshmeat.net for examples, unless you've already done that and found JB> that no other projects come close to your needs. JB> Justin Buist The parent id links back to the main table. I'm sorry, I didn't eleborate enough. There is one more table to note, the downloads table. This table just matches member id's to song id's to see whos bought what. downloads -------------------- member_ID (from user table, unimportant) song_ID (links to both the main table and songs table) so if my data looked like: member_ID | song_ID 1 | 1 1 | 2 1 | 5 2 | 1 3 | 5 3 | 4 3 | 1 I'd want to query something like: SELECT song_ID,songs.*,main.* from downloads,songs,main WHERE downloads.member_ID = X <X being an int> AND songs.main_ID = main.ID and songs.artist_ID = main.ID (obvious problem here) The issue is that I need to get the name field and others from the main table for each song and simultaneously get those same fields from that same table from the artist_ID field in the same recordset, get it? I'm thinking that there is no way to do this with sub-selects, is it possible to do with PHP someway? thanx, J -- Best regards, Jacob mailto:junk@balances.com