'OR' between tables

[ssingh]

I have 2 tables say tableA and tableB
tableA has columns - A,B,C1
tableb has columns - A,B,C2
I need rows from tableA and tableB - A,B,C1,C2
when data is there is either in C1 or C2 or both.

Please suggest how do I write such query.
thanks

[kishore.kr]

hi
check whether this will work.,

select TA.A, TA.B, TA.C1, TB.C2 from (select A, B, C1 from tableA where C1 is not null) as TA, (select A, B, C2 from tableB where C2 is not null) as TB

You can do it using joins also.

[ssingh]

can't do TA.A = TB.A and TA.B = TB.B
since data may or may not be same, column name A, B are same C1 and C2 are diff. Primary key data may/may not be same.

[kishore.kr]

hi
try union,
(select * from tableA where C1 is not null)
union all
(select * from tableB where C2 is not null)

[Ecniv]

Select aryall.a, qryall.b,table1.c as c1, table2.c as c2
From
((
select table1.a, table1.b from table1
union
select table2.a, table2.b from table2
) as qryAll Choices
left join table1 on qryAll.a=table1.a and qryall.b=table1.b)
left join table2 on qryAll.a=table2.a and qryall.b=table2.b

Something like that perhaps?

The union provides a full list (I think) of non duplicated a's and b's
The two joins pull in the columns c.

Another alternative is to use a union to pull all those from table 1 with something in C and union it to all those from table 2 with something in C:

Select table1.a, table1.b, table1.c from table1 where not table1.c is null
union
Select table2.a, table2.b, table2.c from table2 where not table2.c is null

Edit:
Kishore beat me there! lol

[kishore.kr]

hi ,

i think we have to use union all instead of union ( if we are going to second option..). the first qry is little bit complicated for me to understand but its good to learn from queries like these..

[ssingh]

The first query works great, although I didn't get it fully - pretty complex for me.