CODEWITHSUNDEEP
mysql
QuickAccount123
QuickAccount123

Reputation: 189

SQL - Get the names instead of ID

I have a table called tbl_appointment which has 2 foreign keys: idclient and idemployee which they are referencing tbl_persons. So what i want to do is instead of getting the id of idclient and idemployee, i want to get the names (name,last_name,last_sname) which they belong to those id.

Query

SELECT CONCAT(tbl_persons.name,' ',tbl_persons.last_name,' ',tbl_persons.last_sname) as fullname, tbl_appointment.*
FROM tbl_appointment
INNER JOIN tbl_persons ON tbl_persons.idpersons = tbl_appointment.idemployee 
INNER JOIN tbl_persons ON tbl_persons.idpersons = tbl_appointment.idclient
WHERE idclient= '$user';

Upvotes: 1

Views: 1179

Answers (3)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use the following query, using a LEFT JOIN instead of INNER JOIN:

SELECT 
    CONCAT(p1.name, ' ', p1.last_name, ' ', p1.last_sname) AS client_fullname,
    CONCAT(p2.name, ' ', p2.last_name, ' ', p2.last_sname) AS employee_fullname
FROM tbl_appointment ta 
    LEFT JOIN tbl_persons p1 ON ta.idclient = p1.idpersons
    LEFT JOIN tbl_persons p2 ON ta.idemployee = p2.idpersons
WHERE ta.idclient = '$user'

Upvotes: 4

Sergey Menshov
Sergey Menshov

Reputation: 3906

Try the following

SELECT
  app.*,
  CONCAT(client.name,' ',client.last_name,' ',client.last_sname) AS client_full_name,
  CONCAT(empl.name,' ',empl.last_name,' ',empl.last_sname) AS empl_full_name
FROM tbl_appointment app
LEFT JOIN tbl_persons client ON app.idclient=client.id
LEFT JOIN tbl_persons empl ON app.idemployee=empl.id
WHERE app.idclient= '$user'

Upvotes: 2

user40380
user40380

Reputation: 76

Try the following code: Only thing is you have to select the clients and employees separately, together is more difficult

SELECT name,last_name,last_sname 
    FROM tbl_persons WHERE id IN (
      SELECT DISTINCT idclient FROM tbl_appointment;
    ); -- This will select all the clients

SELECT name,last_name,last_sname 
FROM tbl_persons WHERE id IN (
  SELECT DISTINCT idemployee FROM tbl_appointment;
); -- This will select all the employees

Upvotes: 0

Related Questions