JOIN 2 tables

Anon

I have two tables: tb1, tb2:
tb1:
+ id1:
+ id2:
->both are primary
tb2:
+ id1:

+ id2:
+ action: ENUM('del','edit')
->both are primary
Table tb2 is the "delete table" which contains all deleted records from tb1.
How to make a statement to select all record on both tables that id1=3 on both tables and tb2.action='del'? If an record exist in tb1 with a unique (id1,id2) set, then it should not exist in tb2 and all fields must be null. Same in other way, if an record exist in tb2 with a unique (id1,id2) set, then it should not exist in tb1 and all fields must be null. Please help. Thanks.

yurko

dunno why u need 2 ids...
Maybe may story will help u.
I have 2 tabs cl_ip, cl_map_country in one are stored country codes in another their coordinates on map. First table is filled authomaticaly, second I do manualy. So I need to know what countries should I enter
( which exist in cl_ip and do not in cl_map_country). Similar to u right?
So:
select distinct ip_country_code2 from cl_ip LEFT JOIN cl_map_country ON ip_country_code2 = cn_country_code2 WHERE cn_country_code2 IS NULL

ip_country_code2 is field from cl_ip table
cn_country_code2 is field from cl_map_country table

Hope after this example you will be able to solve your problem.

Anon

Hold on, that would be a completely different problem!! Mine is more complicated. There are id1 and id2. This is a forum application software. 'id1' is for topic's id and 'id2' is for message's id. Tb1 is topic table and tb2 is message table. The SELECT statement must return all records with has id1=3 (even if they are deleted, and in tb2 containing deleted message).

Anon

select c1,c2,c3 from tb1
where id1 = 3
union all
select c1,c2,c3 from tb2
where id1 = 3 and action = 'del'

Anon

UNION is only available in 4.00+ right? This is still not a stable version and I only use 3.23. Is there any other way to do this like just using FROM tb1, tb2 WHERE ... with some good trick on the WHERE clause.

Anon

Two queries are the alternatives that I see. (Or change to a decent DBMS.)

Anon

I agree I'm a little new to mysql but that design isn't ... well let's just say it's not ideal.