need help with a complicated query

[quanghoc]

This query required to join more than 2 tables and I'm stuck. I have:
Table 'bigtb': with fields
+ 'id': primary key
+ 'id1'
+ 'id2'
In which id1 and id2 cannot be non-zero at the same record. Zero value means either id1 or id2 not pointing to tb1 or tb2.
Table 'tb1': with fields
+ 'mid'
+ 'id1': primary key
Table 'tb2': with fields
+ 'mid'
+ 'id2': primary key
Table 'mtb': with fields
+ 'mid': primary key
+ 'email'

Now, by knowing only 'id', how to get the 'email' in 'mtb' table? Please help. Thanks.

[leatherback]

Try something like:

select email from bigtb, tb1, tb2, mtb where (id=$id and bigtb.id1=tb1.id1 and tb1.mid=mtb.mid) or (id=$id and bigtb.id2=tb2.id2 and tb2.mid=mtb.mid)

Or something along those lines.

Hm, but it seems to me that somehow there should be a more efficient way to design this database...