can i combine columns from tables into a one column query

C

cuffash

i have four tables with similar headings, i need to combine one column from
each, into a subquery that i can compare to another table column. i have
tried union all but cannot be used in a subquery?

any ideas?
 
M

Mathieu Pagé

cuffash a écrit :
i have four tables with similar headings, i need to combine one column from
each, into a subquery that i can compare to another table column. i have
tried union all but cannot be used in a subquery?

any ideas?
Click to expand...

You will have to create a UNION query, then use this query as the base
of another query that is comparing the result from the union query to
your other table.

Here is an example :

qryA : SELECT a, b FROM tblA UNION ALL a, c AS b FROM tblB;


qryB : SELECT a, b, d FROM qryA INNER JOIN tblC on qryA.a = tblC.a;

Mathieu Pagé
[email protected]
 
C

cuffash

Mathieu Pagé said:
cuffash a écrit :

You will have to create a UNION query, then use this query as the base
of another query that is comparing the result from the union query to
your other table.

Here is an example :

qryA : SELECT a, b FROM tblA UNION ALL a, c AS b FROM tblB;


qryB : SELECT a, b, d FROM qryA INNER JOIN tblC on qryA.a = tblC.a;

Mathieu Pagé
[email protected]
Click to expand...
firstly thanks for your response

i tried something similar, i.e.
select a from TblA
UNION ALL
select a from TblB

but i need to put this, iton something like this
select a from tblC where c.a NOT IN (the above query)

i do have it working but it is extremely long
and i have about 16 tables using the UniON ALL and approx 16000 records

but i will give your suggestion some modification and give it a go
thanks regards cuffash
 
Top