If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: help with count union across multiple joins

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

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:

Code:

select count(1), name
FROM (
select group.name
from callrecords cdr
left join phone p on cdr.origdevicename = p.devicename
left join group grp on grp.deviceid = p.deviceid
union
select group.name
from callrecords cdr
left join gateway gw on cdr.origdevicename = gw.devicename
left join group grp on grp.deviceid = gw.deviceid
) t
group by name

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.