Query to consolidate 2 tables?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

deko - 27 Feb 2005 12:15 GMT

How can I consolidate these two tables into a single view?

Current:

=TableA=
fldP
fldX

=TableB=
fldY
fldZ

Required:

=TableC=
fldX
fldY
fldZ

The number of records in TableA and TableB are not the same, but there is a
1-to-1 correlation, top-down, between the tables, and I only need the first
n records of TableB, where n is the number of records in TableA. I'm not
sure if I need a union query, or some kind of join.

I tried this:

SELECT "fldY", "fldZ", fldX
FROM TableA
UNION SELECT fldY, fldZ, "fldX"
FROM TableB

close, but no cigar...

Reply to this Message

pietlinden@hotmail.com - 28 Feb 2005 02:46 GMT

Oh, that's not a union query, that's a join.
Given: A(p,x), B(y,z)
Goal: C(x,y,z)

select A.x, B.y, B.z
from A inner join B on A.x=B.y

will give you all values of X,Y,Z where values {x,y} match in A and B.

UNION will give you a single column where they may not match.

If this doesn't solve your problem, give a really small sample of your
data - enough to describe the situation. e.g.

A.x contains {1,3,5}
B.z contains {1,2,3,4,5}

HTH

Reply to this Message