How to use SELECT DISTINCT and CONCAT in the same SQL statement

Question

So, I am loading the results of this SQL into an array. The array later becomes a sentence for the text field that works when typing. I want him to return only one name 1 time, even if a person has several purposes. Currently, this returns all appointments for a person with that name, so if Brad Robins has 5 assignments and I start typing Brad, he displays Brad Robins 5 times in sentences, not just once.

$sql = "SELECT DISTINCT CONCAT(clients.studentFirstName, ' ', clients.studentLastName) AS name, appointments.location, appointments.subLocation, appointments.appointmentAddress1, appointments.appointmentAddress2, appointments.appointmentCity, appointments.appointmentState, appointments.appointmentZip, appointments.startTime, appointments.endTime, appointments.date, clients.school FROM appointments JOIN clients ON appointments.clientID = clients.clientID WHERE CONCAT(clients.studentFirstName, ' ', clients.studentLastName) = '".$roommate."' AND clients.school = '".$school."';"; 

It seems to me that DISTINCT and CONCAT just do not play together.

Answer 1

The difference goes against the entire row of ALL columns, not just on behalf. So, if appointments are scheduled for different dates / times, locations, etc., they will all exit. If all you want to show is part of NAME, share the rest of the other content. Request available meetings AFTER the person has been selected.

Answer 2

The problem is the other fields; DISTINCT applies to the entire result. Probably the best thing to do is to separate the requests or populate 2 different arrays; if you specify the name ORDER BY, you can remove duplicates by copying them to the dest array only when the name changes.

Answer 3

Do not use DISTINCT, use the group:

 $sql = "SELECT CONCAT(clients.studentFirstName, ' ', clients.studentLastName) AS name, appointments.location, appointments.subLocation, appointments.appointmentAddress1, appointments.appointmentAddress2, appointments.appointmentCity, appointments.appointmentState, appointments.appointmentZip, appointments.startTime, appointments.endTime, appointments.date, clients.school FROM appointments JOIN clients ON appointments.clientID = clients.clientID WHERE CONCAT(clients.studentFirstName, ' ', clients.studentLastName) = '".$roommate."' AND clients.school = '".$school."' group by CONCAT(clients.studentFirstName, ' ', clients.studentLastName);"; 

Also be careful with XSS in $ school and $ roomate, if available from the outside.

Answer 4

you can use

 group by name 

at the end, which will lead to the return of the request only to one of each name, but then you cannot predict what reception results will be returned in cases where the client has several purposes, and the request ceases to be very useful.

Like others, you should simply get a list of appointments after the client has been selected.

Answer 5

Choose a separate concat (....) from ....

Answer 6

 select colA||' - '||colB from table1 where colA like 'beer%' group by colA||' - '||colB order by colA||' - '||colB ;