Cannot select distinct values on joins

Question

Need to join different tables but during join to select distinct rows per one column

I have the following tables:

Devices
-------
id
Device
Snid
locationid
planid

Locations
---------
id
location

Plans
------
id
plan

Assignments
-----------
id
deviceid
startdate
enddate

What i have tried is this:

SELECT DISTINCT a.id, a.device, a.Snid, b.location, c.plan, d.startdate, d.enddate 
FROM Devices a 
LEFT JOIN Locations b on a.locationid=b.id 
LEFT JOIN Plans c on a.planid=c.id 
LEFT JOIN Assignments d on a.id=deviceid

If in select, I remove the assignment columns (startdate, enddate) then I get normal results but I need to get the Assignment table columns. But, as soon as I add the columns, I cannot get the distinct devices.

What I would like to get as result is Devices table with combination of plans and locations (for the plan and location name) plus the assignment startdate and enddate columns. And to have unique devices with the latest assignment which in mycase should be with a blank enddate column.

Also, I need to get a totalassignments column. I tried adding in the select portion the:

SUM(case when a.id=d.deviceid then 1 else 0) as totalassignments

but if I use this, I need to add a group by. In group by, I need to include all columns and then again I lose my distinct.

Example as follows:

Devices table

id  device  locationid  planid
2   computer    1         0
3   mobile      2         1
10  printer     1         1
12  scanner     1         1
32  tablet      1         1
42  tv          2         2
68  video       3         2
98  camera      0         0

Locations table

ID  location
1   Storage
2   Office
3   Contractor

Plans table

ID  Plan
1   Short-term
2   Long-term

Assignments table

id  deviceid    startdate   enddate
1      10      10/17/2019   2/23/2019
2      32      10/20/2019   
3      12      10/18/2019   11/1/2019
4      68       
5      98      10/15/2019   10/5/2019
6      42      10/23/2019   
7      10      10/1/2019      NULL
8       2      8/18/2019    10/21/2019
9       3      10/23/2020    NULL
10     12      10/2/2019     NULL

Desired result

device  deviceid    location    plan    totalassignments    startdate   enddate
computer    2       Storage     NULL          1             8/18/2019 10/21/2019
mobile      3       Office    Short-term      1             10/23/2020  NULL
printer     10      Storage   Short-term      2             10/1/2019   NULL
scanner     12      Storage   Short-term      2             10/2/2019   NULL
tablet      32      Storage   Short-term      1             10/20/2019  
tv          42      Office    Long-term       1             10/23/2019  
video       68      ContractorLong-term       1                NULL     NULL
camera      98      NULL        NULL          1             10/15/2019

As you can see the result must have the devices (with replaced locationid and planid with relevant location and plan names using inner join), for each device to have number of assignments and next to this to have start and end date of the latest assignment (which is configured when the enddate is NULL)

It looks like a `top-n-per-group` problem. – Vladimir Baranov Oct 23 '19 at 12:33 — Vladimir Baranov, Oct 23 '19 at 12:33

score 1 · Answer 1 · answered Oct 23 '19 at 07:24

This is because the time factor in your column data is not the same.

CAST(d.startdate AS DATE) will nullify the time element, retaining the date only

Try This

SELECT DISTINCT a.id, a.device, a.Snid, b.location, c.plan, 
       CAST(d.startdate AS DATE), CAST(d.enddate AS DATE)
FROM Devices a LEFT JOIN Locations b on a.locationid=b.id 
LEFT JOIN Plans c on a.planid=c.id LEFT JOIN Assignments d on a.id=deviceid

If you want to include aggregate function then,

SELECT DISTINCT a.id, a.device, a.Snid, b.location, c.plan, 
       CAST(d.startdate AS DATE), CAST(d.enddate AS DATE),
       SUM(case when a.id=d.deviceid then 1 else 0) as totalassignments
FROM Devices a LEFT JOIN Locations b on a.locationid=b.id 
LEFT JOIN Plans c on a.planid=c.id LEFT JOIN Assignments d on a.id=deviceid
GROUP BY a.id, a.device, a.Snid, b.location, c.plan, 
       CAST(d.startdate AS DATE), CAST(d.enddate AS DATE)

This is not the issue. I don't have issue selecting the date. So if i do as you see (which is my solution without the casting) then i get duplicate devices if a device is assigned more than once. — Spiris, Oct 23 '19 at 09:22
Just edited initial post to add examples (initial tables and result) — Spiris, Oct 23 '19 at 12:12

Cannot select distinct values on joins

1 Answers1