can i combine columns from tables into a one column query

[Guest]

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?

 

[Guest]

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?

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é
(e-mail address removed)

 

[Guest]

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é
(e-mail address removed)

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