Basic Join Not Working (Copy From LAMP Forum)
-
Hello there. I am trying to get data from 3 different tables based on simple join. One of the tables can have multiple values against one primary key. Here are the table designs Table 1 - EmployeeDetails
EmployeeId INT, FirstName VARCHAR, SurName VARCHAR, SexId INT
Table 2 - EmployeeSex
SexId INT, Sex VARCHAR
-- Values inserted
-- 1, Male
-- 2, FemaleTable 3 - EmployeeContacts
EmployeeId INT, Contact VARCHAR -- may or may not be null AND can contain multiple values
I am using following query
SELECT ED.EmployeeId, ED.FirstName, ED.SurName, GROUP_CONCAT(EC.Contact)
FROM EmployeeDetails ED, EmployeeSex ES, EmployeeContacts EC
WHERE ED.SexId = ES.SexId AND ED.EmployeeId = EC.EmployeeId AND ED.EmployeeId = 'emp_password';Now this query works fine if we have at least one contact number. But if there are not contacts, then this results in empty set. What is wrong with this query ? How can I improve so that it works in all scenarios (regardless of number of contacts in EmployeeContacts table). Thanks for any input.
-
Hello there. I am trying to get data from 3 different tables based on simple join. One of the tables can have multiple values against one primary key. Here are the table designs Table 1 - EmployeeDetails
EmployeeId INT, FirstName VARCHAR, SurName VARCHAR, SexId INT
Table 2 - EmployeeSex
SexId INT, Sex VARCHAR
-- Values inserted
-- 1, Male
-- 2, FemaleTable 3 - EmployeeContacts
EmployeeId INT, Contact VARCHAR -- may or may not be null AND can contain multiple values
I am using following query
SELECT ED.EmployeeId, ED.FirstName, ED.SurName, GROUP_CONCAT(EC.Contact)
FROM EmployeeDetails ED, EmployeeSex ES, EmployeeContacts EC
WHERE ED.SexId = ES.SexId AND ED.EmployeeId = EC.EmployeeId AND ED.EmployeeId = 'emp_password';Now this query works fine if we have at least one contact number. But if there are not contacts, then this results in empty set. What is wrong with this query ? How can I improve so that it works in all scenarios (regardless of number of contacts in EmployeeContacts table). Thanks for any input.
Use joins, not a where condition
SELECT ED.EmployeeId, ED.FirstName, ED.SurName, GROUP_CONCAT(EC.Contact)
FROM EmployeeDetails ED
INNER JOIN EmployeeSex ES on ED.SexId = ES.SexId
LEFT JOIN EmployeeContacts EC on ED.EmployeeId = EC.EmployeeId
WHERE ED.EmployeeId = 'emp_password';If there are instance where an employee does not have an assigned sex, change the INNER JOIN to a LEFT JOIN
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================