Hi,
I'm a bit stuck on a query where I am trying to join call records (many rows) against some other tables.

Basically, these call records contain a field called 'origdevicename'. This device can be either a phone or gateway. There are two other tables 'phone', 'gateway' which I want to join against. I can't tell whether the origdevicename is a gateway or phone unless I compare the name against these other tables.

In the end, I want to count how many calls there are and some property of these phone and gateway devices.

My initial thought was to use a union
select count(1), group.name
from callrecords cdr
left join phone p on cdr.origdevicename = p.devicename
left join group grp on grp.deviceid = p.deviceid
group by group.name
union
select count(1), group.name
from callrecords cdr
left join gateway gw on cdr.origdevicename = gw.devicename
left join group grp on grp.deviceid = gw.deviceid
group by group.name

The problem is the data sets are not merged. In other words, I will get

count name
---------------
541 MyGroup1 <-- this is actually phone MyGroup1
51 MyGroup1 <-- this is actual gateway MyGroup1
4 MyGroup2

I'm looking for just one unique entry for MyGroup1 of 541+51. There's probably a case or subselect but I fear the performance would be bad. For example

select count(1), (select group.name from group where origdevicename in (select name from gateway, etc....)
from callrecords

Any help is appreciated.

Yes that's the solution:

And no, the performance will not be bad - it won't be noticable slower than your query .

When it comes to performance, never "assume" something is fast or slow unless you have tested and proven it to be slow or fast.

And please use [code] tags in the future to make your SQL readable.