htmlpower htmlpower - 3 months ago 10
SQL Question

Inner Join with one table and two fields

Hello friends I have two tables

Users:
id,
name,
email

Incidence:
id_incidence,
id_users,
id_tech,
title,
description

these are my two tables i want to get the name of the user and the tech name in the same query, all of them are in the users table i get to obtain the users name but how could i obtain the tech name in the same sql query? i have this

Select users.name,incidence.name,incidence.description from incidence inner join users on incidence.id_users = users.id


This work but i only obtain the user name with the title and description but i need to get also the tech name using incidence.id_tech = users.id

Answer

You need to add one more instance of table users in inner join

SELECT
    A.name,
    incidence.name,
    incidence.description,
    B.name
FROM incidence 
INNER JOIN users A ON incidence.id_users = A.id
INNER JOIN users B ON incidence.id_tech = B.id;

Note: B.name is the name of the corresponding user referred by incidence.id_tech field.