Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010
    Posts
    40

    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

    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.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by dtrobert View Post
    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.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •