Join all records for two tables? ( Performance question)

  • Thread starter Thread starter Ivan
  • Start date Start date
I

Ivan

Hi,
I have a performance question related to joining two tables.
Currently we have table "A" and "B" with
the extractly same field name ( f1, f2, f3)

Now we want to sum up the a.f3 & b.f3
and join the field f1 and f2. Some record may not join to another table,
but we only want to display in the query.
Therefore I want to Left-Join for joining both two table
and one normal join.
Then using "Union" to join those three queries.
Is it the best way to do?

Sorry for my poor english hope that you can understand my question.
 
Hi,


I would start making a table, c, out of:


SELECT f1, f2 FROM A
UNION
SELECT f1, f2 FROM B



and add indexes to the two fields.

Next, my query would be:


SELECT c.f1, c.f2, SUM(a.f3)+SUM(b.f3)
FROM ( c LEFT JOIN a ON c.f1=a.f1 AND c.f2=a.f2)
LEFT JOIN b ON c.f1=b.f1 AND c.f2=b.f2



Hoping it may help,
Vanderghast, Access MVP
 
If you want all the rows (I think is what you are saying) you mayb
better served using the UNION up front and then summing the fields onc
you have all the rows (based on the UNION query). You would only nee
two queries then
 
If you want all the rows (I think is what you are saying) you mayb
better served using the UNION up front and then summing the fields onc
you have all the rows (based on the UNION query). You would only nee
two queries then
 
thanks for your reply.

since the data keeps updating, therefore, will it be faster if we don't
create the new table "c"?

Anyway, your suggestion seems easier than mine.



Hi,


I would start making a table, c, out of:


SELECT f1, f2 FROM A
UNION
SELECT f1, f2 FROM B



and add indexes to the two fields.

Next, my query would be:


SELECT c.f1, c.f2, SUM(a.f3)+SUM(b.f3)
FROM ( c LEFT JOIN a ON c.f1=a.f1 AND c.f2=a.f2)
LEFT JOIN b ON c.f1=b.f1 AND c.f2=b.f2



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top