Querying MySQL with join

[Tekime]

Hello all,
I got some great help finalizing a few SQL structures for an app I'm rewriting (thanks barand, hereve and bastien and anyone else that helped me).

I'm having a tough time with a SELECT statement though. I have a function that is given a user_id, and should select an associative array of values from table groups where user_id is a member of the group. The two tables I'm concerned with are:

CREATE TABLE groups
(
 group_id tinyint(2) unsigned not null auto_increment,
 name varchar(32) not null,
 author enum('Y','N') not null default 'N',
 publish enum('Y','N') not null default 'N',
 content_admin enum('Y','N') not null default 'N',
 admin enum('Y','N') not null default 'N',
 PRIMARY KEY(group_id)
);

CREATE TABLE groups_users
( 
    user_id int unsigned not null,
    group_id tinyint unsigned not null,
    INDEX GU_UID (user_id),
    INDEX GU_GID (group_id)
);

So, say there is an entry in groups_users where user_id=1 and group_id=3. I would like to get an associative array of values from group corresponding to the proper group_id.

I know this is really simple stuff but I cannot for the life of me get anything back except ALL the groups rows :mad:

Thanks for any help 🙂

[erick_w]

SELECT groups.name
groups.author

groups.publish
groups.content_admin
groups.admin
FROM group_users,groups
WHERE group_users.user_id='1'
AND group_users.group_id=groups.group_id;

[Tekime]

Can't thank you enough erick_w, works like a charm! I'm too caught up learning these explicit joins, and tend to overlook the obvious 😃

[erick_w]

you're welcome buddy 🙂 .... I need some sleep for now though.. :p