By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,702 Members | 1,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,702 IT Pros & Developers. It's quick & easy.

single query for multiple table lookup based on third table

P: n/a
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.
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.
Jul 20 '05 #2

P: n/a
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.
Jul 20 '05 #3

P: n/a
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.


Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.