writing efficient query?

[gdanalakshmi]

I have 2 tables.
TableA has 2 columns - BldgName and RecvCount
TableB has 2 columns - BldgName and SendCount

My output should be
BldgName RecvCount SendCount.


TableA has some records that are not in TableB
TableB has some records that are not in TableA

So I have to find the commmon records of both as well as non- matching records from both the tables.

How can i do it efficiently?

 

[Jon K]

You can combine a Left Join with a Right Join in a Union Query:-

SELECT TableA.BldgName, RecvCount, SendCount
FROM TableA Left JOIN TableB ON TableA.BldgName = TableB.BldgName
UNION
SELECT TableB.BldgName, RecvCount, SendCount
FROM TableA Right JOIN TableB ON TableA.BldgName = TableB.BldgName;


(If would be easier to directly type the query in SQL View.)

 

[bayman]

The proposed query will return duplicate rows where BldgName is in both tables A and B.

Instead you could try:

SELECT TableA.BldgName, RecvCount, SendCount
FROM TableA Left JOIN TableB ON TableA.BldgName = TableB.BldgName
UNION
SELECT BldgName, 0, SendCount
FROM TableB
WHERE BldgName not in (SELECT BldgName FROM TableA)

 

[Jon K]

Bayman,

Had you actually tried my query when you posted your comment?

Jon

 

[gdanalakshmi]

Both the queries return the same output.

Thanks for the suggestion.

 

[Jon K]

NOT IN is inefficient as it cannot be optimised by Access. If the two tables are fairly large, you should avoid using it.