[Resolved] Subselect workaround for 3.23?

captainsanity

Hi.

I'm hoping that someone would be good enough to help me out with my dilemma. I'm pretty sure that what I need to do could be done with a subselect in MySQL 4.1 but I only have 3.23 at my disposal at the moment.

I'm trying to figure out how to select the contacts defined by a dynamic number of group memberships for the appropriate user. Here's a simplified version of the database:

USERS Table

UID Name

1 Chris
2 Joe
3 Tom

USER_GROUP Table

UID GID

1 1
1 2
2 2
3 2

CONTACTS Table

CID GID

1 2
2 2
3 1
...
600 1
601 2
602 2

Each user will be a member of up to 20 different groups. CONTACTS will grow to hold 1000's of records.

I think my query can be done using MySQL 4.1 with the following statement:

select * from CONTACTS where GID in (select * from USER_Group where UID=$user);

1) Does that look like it would work?

2) How can I do that efficiently with MySQL 3.23?

Thanks in advance for your help. Much appreciated.

Chris

tsinka

Hi,

shouldn't that one work ?

SELECT a.* FROM CONTACTS a,USER_GROUP b WHERE a.GID=b.GID and b.UID=$user

Sorry, can't test it now so there might be an error in it. You might need another type of join.

ahundiak

While tsinka's query should work it's often easier to simply use two queries.

SELECT DISTINCT gid FROM user_group WHERE uid = $uid;

Read through the results,build a comma delimited list of gid's then:

SELECT * FROM contacts WHERE gid IN($gids);

Yes, two queries will be slower than one query(probably). But as you start to add more complicated joins and wheres and order by clauses,
it can get to be quite a pain doing it in one statement. Which of course is why we have sub-queries in the first place.

captainsanity

Both suggestions worked perfectly. Thank you both for your help.

Chris