I have a table with names in it where the names can function as 1 of 2 things - either an artist or a group. In order to make searching easy I wished to keep these names in one table. EG: searching for artist name, whether a group or soloist you would have to search only one table. The problem arises if the person wishes to get the names of the artists in the group or an artist also performs in multiple groups. I thought a self relationship of sorts solved this but then I could not figure out how to write the query. For instance the simplest query is: show me all the groups and artists in each group, or Show me the artists names in group U2. Easy if groups and artist names were in 2 tables, however...
A table B table ---------------------------- ------------------------------ id Name id grp_id art_id ---------------------------- ------------------------------ 1 Joe 1 2 1 2 U2 2 2 3 3 Mike 3 2 4 4 John ------------------------------ 5 Jane --------------------------- In the above sample data, table B is a "join" of sorts where the A.id functions as both the B.grp_id and B.art_id. EG: B.grp_id = A.id AND B.art_id = A.id I thought I had come up with a nifty little trick (using the same id in different fields to designate a distinction ) but alas..... I can only select one column to get the names of the group and artists. I thought nested selects would solve this - maybe not - but no nested selects in mysql anyway. So..can this be done with this design or must I split the artists and groups into separate tables, forcing a multiple table search? I hoped column alias would solve this but I could not make it work using them. Confused? Good...so am I! Thanks in advance for any help Franklin Williams [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
