Table 1 Table 2
ID COL1 COL2 ID COL1 COL2
1 1000 A 1 1002 A
2 1001 B 2 1000 A
3 1000 A 3 1001 B
I want to be able to count the number of unqiue occurances of COL1 in both tables where COL2 = 'A'. The solution I came up with is as follows:
SELECT DISTINCT COL1 FROM Table1 WHERE COL2='A' GROUP BY COL1
UNION SELECT DISTINCT COL1 FROM Table2 WHERE COL2='A' GROUP BY COL1
I then simply look at the rs.RecordCount to give me the number of unique COL1 values accros both tables. Is there a way to actually get the query itself to return the value itself, rather than having to use RecordCount? And, which method would be considered best? Mine, or having the query return the result?
I tried this way:
SELECT DISTINCT Count(COL1) AS Cnt
But it gives only a count of each COL1 item, rather than each unique item.