can i combine columns from tables into a one column query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cuffash - 06 Jul 2006 13:41 GMT

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?

Reply to this Message

Mathieu Pagé - 06 Jul 2006 14:37 GMT

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é
mathieu.page@csfinc.com

Reply to this Message

cuffash - 10 Jul 2006 21:25 GMT

> cuffash a écrit :
> > i have four tables with similar headings, i need to combine one column from
[quoted text clipped - 15 lines]
> Mathieu Pagé
> mathieu.page@csfinc.com

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

Reply to this Message