SQL Count with IF-ELSE

Question

I am trying to write a SQL command that in person table device history is stored per person. current device is not null devicex_mac. So there is possible that device2_mac could be current device (device3_mac is null) or device1_max could be current device(device2_mac and device3_mac are both null).

**Person Table**            
id  device1_mac device2_mac device3_mac


**Device Table**        
mac   brand   model

With the fact above how may I detect current device and JOIN both tables in order to get the following result.

**RESULT:**

BRAND   MODEL   COUNT
BRAND1  Model1  350000
BRAND1  Model2  700000
BRAND2  Model1  480000

Answer 1

If you wanted to see the distribution of all the columns, you would do:

select d.brand, d.model, count(*)
from ((select id, device1_mac as mac
       from person
      ) union all
      (select id, device2_mac
       from person
      ) union all
      (select id, device3_mac
       from person
      )
     ) pd left outer join
     devices d
     on pd.mac = d.mac
where mac is not null
group by d.brand, d.model

If the last column contains the information you want, you would do:

select d.brand, d.model, count(*)
from person p left outer join
     devices d
     on coalesce(p.device3_mac, p.device2_mac, p.device1_mac) = d.mac
where mac is not null
group by d.brand, d.model

coalesce() chooses the first non-NULL value.

EDIT:

If the question is about performance and there is an index on devices(mac), then I would suggest a variation on the first query:

select d.brand, d.model, count(*)
from ((select id, device1_mac as mac
       from person
       where device2_mac is null and device1_mac is not null
      ) union all
      (select id, device2_mac
       from person
       where device3_mac is null and device2_mac is not null
      ) union all
      (select id, device3_mac
       from person
       where device3_mac is not null
      )
     ) pd left outer join
     devices d
     on pd.mac = d.mac
where mac is not null
group by d.brand, d.model

The use of functions or or in an on clause often cause databases to ignore indexes.

Answer 2

I'm assuming that only one of the device columns has a value at any time. Then you can do:

SELECT
   brand,
   model,
   COUNT(*)
FROM
  tblDevice 
  JOIN tblPerson ON
     tblDevice.mac = tblPerson.device1_mac OR
     tblDevice.mac = tblPerson.device2_mac OR
     tblDevice.mac = tblPerson.device3_mac
group by 
   brand, 
   model