SQL how to inner join based on a condition-CodePudding

I have two tables - one called customers consisting of the fields id, name and referrals and another table called devices consisting of the fields id and type - where type is either 'mobile' or 'laptop'. The id field in the devices table corresponds to the id of a customer in the customers table. Note one customer can have 2 entries in the devices table if they have used both a mobile and a laptop.

I'm trying to query something which returns a persons 'name', the devices they have used (lets call this 'devices_used') and 'referrals' where 'name' and 'referrals' come from the customers table and 'devices_used' is as follows: when a customer ONLY has a mobile entry in the devices table then this field should be populated as 'mobile', when ONLY laptop then this should be 'laptop' but if a client has both a mobile and laptop entry in the devices table then this should be populated as 'both'.

I'm not really sure how to do this as something like

select c.name, d.type, c.referrals 
from customers c 
inner join devices d on c.id = d.id

isn't what I'm looking for since this would return two rows for the same customer if they have used both a mobile and a laptop - I'm looking for a query which which return just one row per customer and in the case they have used both devices the 'devices_used' field should be populated as 'both'.

CodePudding user response：

Making some assumptions, I believe you could simply use a GROUP BY to deduplicate what you are looking for.

select c.name, d.type, c.referrals 
from customers c 
inner join devices d on c.id = d.id
group by c.name, d.type, c.referrals

Alternatives might include looking at your channel (device used) one at a time and using a UNION to put them back together, but it depends on your goal.

CodePudding user response：

WITH device_numbers AS (
     SELECT c.id,
           COUNT(d.type) AS num_devices
     FROM customers c
     JOIN devices d
     ON c.id = d.id
     GROUP BY 1
)

SELECT c.name, 
     CASE WHEN num_devices = 2 THEN 'both'
     ELSE d.type END AS devices_used
FROM customers c
JOIN devices d
ON c.id = d.id
JOIN device_numbers dn
ON c.id = dn.id

CodePudding user response：

I'm assuming you need to aggregate your devices table, you can easily do this by applying it for each customer.

The following makes some guesses you may need to amend as appropriate, eg catering for NULL values

select c.name, d.[type], c.referrals 
from customers c 
outer apply (
  select case when Count(*)=1 then Max([type]) else 'both' end
    from devices d
    where d.id = c.id
    group by d.id
)d([type]);