Multiple table search

luiddog

I can't seem to find a way to do this.

I have two tables, one a user table that has multiple types of users (soloist, bands) and a table that has band members. Also a soloist can be a member of multiple bands. The band members are connected to the bands with an unique User table ID. What I want to do is perform a search for a person, let's say John Doe and have his listing as a soloist and for each of his bands appear with the band name. So:

User Table:
_________________
ID | Name | Type|

1 | John Doe | Solo | 
2 | The Doe's | Band |
3 | Doe Ray's | Band | 
_________________



Band Member Table:
_________________
BID | ID | Name |

1 | 2 | John Doe | 
2 | 3 | John Doe |
_________________

A search for "John Doe" would result in:

John Doe - Soloist
John Doe - The Doe's
John Doe - Doe Ray's

Any ideas? Thanks.

TheDefender

In order to make this work, you should normalize the data and keep the Users and Bands separated into two different tables. Then use a third table to identify the ralationship between the two tables.

In the following example, John Doe and Mama Cass are Soloists, and members of The Doe's and Jane Doe is only a member of Doe Ray's.

User Table:

UID | Name

1 | John Doe

2 | Jane Doe
3 | Mama Cass

Band Table:

BID | Name

1 | Solo

2 | The Doe's
3 | Doe Ray's

User_Band Table:

UBID | UID | BID |

1 | 1 | 1
2 | 1 | 2
3 | 2 | 3
4 | 3 | 1
5 | 3 | 2

luiddog

That would be ideal but the site will not allow it. Also, my original post had the wrong table info, I've since fixed it. I've found that I can use a LEFT JOIN with an IF statement within a repeat DO that will work but I'm hoping to find a better way.