CODEWITHSUNDEEP
mysqlsql
Bonik
Bonik

Reputation: 812

SQL Join table with null data retrive none

so my problem is that lets say i have 3 tables: "customerPerAdmin","admins","customers" and i'm joining them, but what if, customerPerAdmin.AdminID will be 0 for a certain user? what will happen that it will ignore the customer with adminID equals to zero, and i dont want that to happen, i want to set admins.FirstName and admin.LastName as empty.

customerPerAdmin Table:

ID  AdminID CustomerID
40  20     49           
41  25     48        
42  20     50
43  0      51

and this is the query 

SELECT SQL_CALC_FOUND_ROWS `customers.UniqID`,
       `customerPerAdmin`.`AdminID`,
       `customerPerAdmin`.`CustomerID`,
       `customerPerAdmin`.`IsDeleted`,
       `customerPerAdmin`.`pr`,
       `admins`.`ID`,
       `admins`.`GroupID`,
       `admins`.`FirstName` AS adminFirstName,
       `admins`.`LastName` AS adminLastName,
       `admins`.`IsDeleted`,
       `customers`.`FirstName`,
       `customers`.`LastName`,
       `customers`.`Phone`,
       `customers`.`Email`,
       `customers`.`GroupID`,
       `customers`.`UniqID`,
       `customers`.`Created`,
       `customers`.`IsDeleted`,
       `customers`.`ID`
FROM (`customers`)
JOIN `customerPerAdmin` ON `customers`.`ID`=`customerPerAdmin`.`CustomerID`
JOIN `admins` ON `admins`.`ID`=`customerPerAdmin`.`AdminID`
AND `customers`.`GroupID` = '8'
AND `customerPerAdmin`.`pr` = 0
GROUP BY `customers`.`ID`
ORDER BY `customers`.`ID` DESC LIMIT 20

so what will happen, it will only show me the IDs of 40,41,42 and not 43 because he cant relate it to an admin with ID of 0, because he is not exists. how can i still show ID 43 even if it not related?

thanks in advance.

Upvotes: 0

Views: 37

Answers (1)

user275683
user275683

Reputation:

In your code you need to change from INNER JOIN to LEFT OUTER JOIN

By using INNER JOIN you are forcing all rows to be matched. LEFT OUTER JOIN lets you get all the rows from your primary table plus all matching records from Joined table(s).

Here is quick and dirty JOIN lesson

INNER JOIN or JOIN
Get Data from tables where all rows are match

LEFT OUTER JOIN or LEFT JOIN
Get data from primary table and only get data from joined table(s) where data can be matched.

FULL OUTER JOIN or FULL JOIN
Get data all data from both tables and match what ever possible.

There is also RIGHT join but I never use it. It is same as LEFT just making another table the primary source. Some people use both LEFT and RIGHT in the same query, at which point I start to doubt if they know what they are doing.

In addition to link in the comment you can review Join Fundamentals from Microsoft

Upvotes: 1

Related Questions