mysql ?: need help with complex joins in select statements

[anthracite98]

Here's the deal. I have a table full of user info. Another table of possible music genres, another of possible subgenres (all associated with the genre table) and another table that links userInfo with subGenre. (i.e. linkID,userID,subGenreID)-since any user can have any number of associated subgenres.

Now, I need a search that can 1. allow users to search on multiple subgenres (i.e. check boxes or whatnot) and 2. can get all the subgenre info returned with the select.

basically i need returned, userInfo.uID,userinfo.username,userinfo.location along with all the subgenres named.

subgenre table looks like subgenreid,genreid (the associated genre category),subgenreName.

I realize i can do a faux sub-select by just getting the user info, then in my while statement, running another select to get the subgenre info, but I'm trying to avoid this as much as possible.

Any help is greatly desired.

also, semi-=separate question: if i do a join, such as

select * from userinfo left join genrelinktable on userinfo.uid = genrelinktable.uid

i would get my user, and then, say, 3 subgenres (well, the #'s representing the subgenre anyway). how do i output this? meaning if i set the mysql resulting array to a variable, then follow the familiar $var['columnName'], how do i get the 3 subgenres since they are the same column name of that table.

-->or using lots of familiar book examples, if the user has 5 children in children TABLE, what is my select statement to find that user, all his children? and then how do i output my findings?

 

[piti]

hi

i hope i got it right, you want to select user info with all his subgenres?
if so

select userInfo.uID, userInfo.userName, userInfo.location, subgenre.subgenreName
FROM userInfo
INNER JOIN linkUserSubgenre ON linkUserSubgenre.uID = userInfo.uID
INNER JOIN subgenre ON subgenre.subgenreID = linkUserSubgenre.subgenreID
[WHERE userInfo.uID = <userid>]

if you want to return also users without subgenres set, use LEFT joins instead of INNER

and to that output:
while ($var=mysql_fetch_array($query_result)) {
echo $var['columnname1'].$var['columnname2']
}

hope that helps