CODEWITHSUNDEEP
sqldistinct
Jonathan Ambriz
Jonathan Ambriz

Reputation: 29

SQL DISTINCT only for one column

I am trying to get the results from this query to only give me DISTINCT results by the concatenated column fullname. The results instead give me DISTINCT by both fullname and facilityname (which is 'name'). I've looked over other solutions to similar questions but don't understand them well enough to apply them to this situation.

I've tried to play with the code, but nothing worth noting.

USE country_club;

SELECT DISTINCT 
    CONCAT(mem.firstname, mem.surname) AS fullname, 
    fac.name
FROM 
    Bookings AS boo
JOIN 
    Members AS mem ON boo.memid = mem.memid
JOIN 
    Facilities AS fac ON boo.facid = fac.facid
WHERE 
    boo.facid = 0 OR boo.facid = 1
ORDER BY 
    fullname ASC;

No errors on this code to note. Just need to modify the conditions to arrive at the desired outcome.

Upvotes: 0

Views: 43

Answers (2)

Hossein.Kiani
Hossein.Kiani

Reputation: 137

If you're using the mysql, before running your query, run this:

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY', ''));

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

If you want one row per group of columns, use group by, not distinct:

SELECT concat(m.firstname, m.surname) as fullname,
       MIN(f.name)
FROM Bookings b JOIN
     Members m
     ON b.memid = m.memid JOIN
     Facilities f
     ON b.facid = f.facid
WHERE b.facid IN (0, 1)
GROUP BY concat(m.firstname, m.surname)
ORDER BY fullname ASC;

Upvotes: 1

Related Questions