Count unique records in multiple tables

Databases
1

Hi guys

I have the following sql statement

select eventid from table1 where profileid = ‘123456’
UNION
select eventid from table2 where profileid = ‘123456’
UNION
select eventid from table3 where profileid = ‘123456’

I can use this to display all records which have a profileid value of “123456”. However, how would I count how many records with UNIQUE eventid’s and have a profileid value of “12345” for all three tables?

For example, if my tables had the following…

Table1
Eventid | Profileid
555555666 | 123456
555555666 | 123456
555555666 | 888888

Table2
Eventid | Profileid
555555666 | 123456
222222555 | 123456
555555666 | 123456

Table3
Eventid | Profileid
555555666 | 123456
222222333 | 123456
555555666 | 888899

The answer would be:

3

For, “555555666”, “222222555” & “222222333”

Any help would be fully appreciated

Best regards

Rod from the UK

2

SELECT COUNT(DISTINCT eventid) AS answer FROM ( SELECT eventid FROM table1 WHERE profileid = '123456' UNION SELECT eventid FROM table2 WHERE profileid = '123456' UNION SELECT eventid FROM table3 WHERE profileid = '123456' ) AS u

3

Hi R937

This worked excellently!

Thank you so much

Best regards

Rod from the UK

4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.