SQL Join table with null data retrive none

Question

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.

thank you so much! that worked!, can you give me more detailed explanation about outer and inner join? because i dont really understand them — Bonik
– Bonik, Commented Mar 18, 2014 at 20:57

user275683 · Accepted Answer · 2014-03-18 21:42:15Z

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

Collectives™ on Stack Overflow

SQL Join table with null data retrive none

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related