Franklin Williams Jr. wrote:
> 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]
>
>
Hi,
select A1.Name as GroupName, A2.Name as Artist
from A as A1,
B,
A as A2
where A1.id = B.grp_id AND B.art_id = A2.id
should solve your problem.
(Don't forget to create indexes on A(id), B(art_id) and B(grp_id) ;) )
Hope this helps
--
Joseph Bueno
---------------------------------------------------------------------
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
