CODEWITHSUNDEEP
mysqljoin
Gabriel Matusevich
Gabriel Matusevich

Reputation: 3855

SQL JOIN, Replace id with value

I have a query that is giving me trouble, im not sure how to do this

I have to retrieve records from a TICKETS table and join it together with 2 others,, That is not the problem, i need to replace one of the values in the record for a value in another table.... For Eg:

Table tickets:

numint user_id type desc attach priority status date assignation

Table users

numint company_id name email username password ps security token date

Table companies

numint name

Example Record

company_name - user_name - type - title - priority - status - date - assignation

"someCompany" - "someUser" - "someTitle" - 1 - "open" - "yyy/mm/dd" - 2(user_id)

in the assignation field of the returned record i need to replace it with the correspondant value from the users table IF it's NOT 0 (zero)

This is my Query so far

SELECT tickets.numint, tickets.type, tickets.title, tickets.description,
       tickets.priority, tickets.status, tickets.date,
       users.name AS user_name, companies.name AS company_name,
       CASE WHEN tickets.assignation=0 THEN 'not-assigned'
       ELSE ????????? END AS assignation
       FROM tickets
       LEFT JOIN users ON tickets.user_id = users.numint
       LEFT JOIN companies ON users.company_id = companies.numint

i dont know how to replace that value, or if the CASE should after the joins...

Upvotes: 0

Views: 3526

Answers (1)

Chamal
Chamal

Reputation: 1449

Do you want to show username as assignation. use users.name in case.

SELECT tickets.numint, tickets.type, tickets.title, tickets.description,
       tickets.priority, tickets.status, tickets.date,
       users.name AS user_name, companies.name AS company_name,
CASE WHEN tickets.assignation=0 THEN 'not-assigned'
ELSE users1.name END AS assignation
FROM tickets
LEFT JOIN users ON tickets.user_id = users.numint
LEFT JOIN companies ON users.company_id = companies.numint
LEFT JOIN users AS users1 ON tickets.assignation = users1.numint

Upvotes: 2

Related Questions