single query for multiple table lookup based on third table

[Kimo R.]

Hello,

Exim 4 integrated with MySQL 4.

I have four tables:

CREATE TABLE users (
uid bigint(10) unsigned DEFAULT '' NOT NULL auto_increment,
listemail varchar(200) DEFAULT '' NOT NULL ,
PRIMARY KEY (uid),
);

CREATE TABLE groups (
gid int(11) DEFAULT '' NOT NULL auto_increment,
listemail longtext ,
UNIQUE gid (gid),
);

CREATE TABLE mailalias (
mid int(11) DEFAULT '' NOT NULL auto_increment,
listemail longtext ,
UNIQUE mid (mid)
);

CREATE TABLE mailgroups (
id int(10) DEFAULT '' NOT NULL auto_increment,
uid int(11) ,
gid int(11) ,
mid int(11) DEFAULT '0' NOT NULL ,
PRIMARY KEY (id)
);

mailgroups contains relations between mailalias, users and groups:
select * from mailgroups;
+----+------+------+-----+
| id | uid | gid | mid |
+----+------+------+-----+
| 1 | NULL | 3 | 17 |
| 2 | NULL | 32 | 17 |
| 3 | 61 | NULL | 17 |
| 4 | 72 | NULL | 17 |
+----+------+------+-----+
What I need to do is:

Given mailalias.listemail, get users.listemail and groups.listemail
that are part of mailalias.listemail. Ie: if mid is 17, find gid 3 and
32, uid 61 and 72. I found in the manual a way to do it with two
queries:

SELECT users.listemail FROM users,mailgroups,mailalias WHERE
mailgroups.uid = users.uid AND mailgroups.mid = mailalias.mid AND
mailalias.listemail
='$LISTEMAIL'

and then the same query with groups instead of users.

But because this is run within Exim, I need this in one query.
Basically, given an email address, I need to find the users and groups
that are associated with that address.

Any help would be appreciated.
Thanks
Kimo R.

[Bill Karwin]

Kimo R. wrote:

SELECT users.listemail FROM users,mailgroups,mailalias WHERE
mailgroups.uid = users.uid AND mailgroups.mid = mailalias.mid AND
mailalias.listemail
='$LISTEMAIL'

and then the same query with groups instead of users.

But because this is run within Exim, I need this in one query.
Basically, given an email address, I need to find the users and groups
that are associated with that address.

I tried this on my test database and it seems to do what you describe:

select coalesce(u.listemail, g.listemail)
from mailalias a inner join mailgroups m on (a.mid = m.mid)
left outer join users u on (m.uid = u.uid)
left outer join groups g on (m.gid = g.gid)
where a.listemail = '$LISTEMAIL'

Regards,
Bill K.

[Kimo R.]

Bill Karwin <bi**@karwin.com> wrote in message news:<cf*********@enews1.newsguy.com>...

Kimo R. wrote:

But because this is run within Exim, I need this in one query.
Basically, given an email address, I need to find the users and groups
that are associated with that address.

select coalesce(u.listemail, g.listemail)
from mailalias a inner join mailgroups m on (a.mid = m.mid)
left outer join users u on (m.uid = u.uid)
left outer join groups g on (m.gid = g.gid)
where a.listemail = '$LISTEMAIL'

Perfect. Thank you very much.
Kimo R.

[Isaac Blank]

Just combine these two queries with UNION ALL:

SELECT users.listemail FROM users,mailgroups,mailalias
WHERE
mailgroups.uid = users.uid
AND mailgroups.mid = mailalias.mid
AND mailalias.listemail='$LISTEMAIL'
UNION ALL
SELECT groups.listemail FROM groups,mailgroups,mailalias
WHERE
mailgroups.gid = groups.gid
AND mailgroups.mid = mailalias.mid
AND mailalias.listemail='$LISTEMAIL'
"Kimo R." <ki*****@hotmail.com> wrote in message
news:e5**************************@posting.google.c om...

SELECT users.listemail FROM users,mailgroups,mailalias WHERE
mailgroups.uid = users.uid AND mailgroups.mid = mailalias.mid AND
mailalias.listemail
='$LISTEMAIL'

and then the same query with groups instead of users.

But because this is run within Exim, I need this in one query.
Basically, given an email address, I need to find the users and groups
that are associated with that address.