Franklin, the "basic relational rules" (http://hometown.aol.com/mbaddenda/art120.html, http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter7/node1.html) say, decompose relationships like your artists & groups to separate tables, and your example fits right in.
PB ----- ----- Original Message ----- From: "Franklin Williams Jr." <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, October 25, 2002 9:44 PM Subject: self relation query help > 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 > > > --------------------------------------------------------------------- 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
