Query with left join doesn't recognize <>

ernest1a · July 6, 2010, 10:03pm

The below query should show network of company in 2nd degree. So if company has 1 employeer and this employeer knows 1 person who knows 3 persons, this employee has 3 persons in 2nd degree and 1 in 1st.

knows
who | whom

employees
id | companyId | userId


SELECT DISTINCT users.userId, users.firstName, users.userUrl, users.lastName 
    FROM users 
    LEFT JOIN knows AS k1 ON users.userId=k1.whom 
    LEFT JOIN knows ON knows.whom=k1.who 
    LEFT JOIN employees ON knows.who=employees.userId 
    WHERE employees.companyId=67
    AND employees.userId<>users.userId

The only problem is that it shows again in 2nd degree also userId which is actually employeer of this company. I have no idea why AND employees.userId<>users.userId does not take action. Hope anybody has any idea.

Tnx!

ernest1a · July 8, 2010, 4:51pm

Fantastic, thanks guido2004 so much! :))

DarthGuido · July 8, 2010, 8:16am


SELECT DISTINCT
    users.userId
  , users.fullName
FROM employees AS e
INNER JOIN knows AS k1
ON e.userId = k1.who
INNER JOIN knows AS k2
ON k1.whom = k2.who
INNER JOIN users
ON k2.whom = users.userId
LEFT OUTER JOIN employees e2
ON k2.whom = e2.userId
AND e2.companyId = 67
WHERE e.companyId = 67
AND e2.userId IS NULL

ernest1a · July 7, 2010, 11:40pm

thanks to all, you are great!

Anyway there is still a problem. I think guido2004 was very close, but the problem is that it returns userId only if this userId exist in employees table. But it should show it in any way because some users can be in network even if they don’t belong to any company. When I deleted from employees userId 4 a query didn’t return it even if this user is connected with employee of company with companyId 67.

I will try also myself to play further around it

thanks again

DarthGuido · July 7, 2010, 7:19pm

A question: does your network logic start from the users table, or from the employees table?

If you want to get all the 2nd degree users known by the employees of a certain company, then I think you should turn the logic in the query around (at least I’ve done so because it makes it easier to understand for me )


SELECT DISTINCT 
    users.userId
  , users.fullName
FROM employees AS e
INNER JOIN knows AS k1
ON e.userId = k1.who
INNER JOIN knows AS k2
ON k1.whom = k2.who
INNER JOIN users
ON k2.whom = users.userId
INNER JOIN employees e2
ON k2.whom = e2.userId
WHERE e.companyId = 67 
AND e2.companyId <> 67

ernest1a · July 7, 2010, 6:56pm

because I need to show network of this company and WHERE employees.companyId=67 shows a network of which company I would like to show.

r937 · July 7, 2010, 3:06pm

why wouldn’t you use

… WHERE employees.companyId <> 67

instead of

… WHERE employees.companyId = 67

ernest1a · July 7, 2010, 1:34pm

Thanks again! My question was actually how to show in 2nd degree only users that are not at the same time employees of company with id 67. I thought I can just simply put employees.userId<>users.userId but seems it is not so easy

Now I also try the following, but also without sucess:
WHERE employees.companyId=‘67’
AND employees.userId<>users.userId
AND employees.userId<>knows.whom
AND employees.userId<>k1.who
AND employees.userId<>k1.whom

Could you give me one more tip please how I can try to solve this. Is answer in group or something like this?

r937 · July 7, 2010, 12:40pm

okay, let’s try to figure out what the query is doing

first of all, the fact that employees is the 4th table in a series of LEFT OUTER JOINs, along with a specific condition on employees.companyId=67 in the WHERE clause, means that you are in effect getting the results of inner joins

change the query to use INNER JOINs and it returns the same results

okay, now let’s build up the query as we go, join by join

here’s the first step –

SELECT users.userId
     , users.fullName   
     , k1.who
  FROM users
INNER
  JOIN knows AS k1 
    ON users.userId=k1.whom

userId   fullName         who
   1     Michael Miller    2
   1     Michael Miller    4
   2     Jefrey White      1
   2     Jefrey White      4
   2     Jefrey White      5
   4     Linda Jenkinson   1
   4     Linda Jenkinson   2
   5     Dave Hoff         2

so basically this shows the friends of each user, right?

now we add the 2nd join

SELECT users.userId
     , users.fullName   
     , k1.who
     , knows.who
  FROM users
INNER
  JOIN knows AS k1 
    ON users.userId=k1.whom
INNER
  JOIN knows 
    ON knows.whom=k1.who

userId   fullName         who   who
   1    Michael Miller    2    1  
   1    Michael Miller    2    4  
   1    Michael Miller    2    5  
   1    Michael Miller    4    1  
   2    Jefrey White      1    4  
   2    Jefrey White      4    1  
   4    Linda Jenkinson   1    4  
   4    Linda Jenkinson   2    1  
   4    Linda Jenkinson   2    4  
   4    Linda Jenkinson   2    5  
   5    Dave Hoff         2    1  
   5    Dave Hoff         2    4  
   5    Dave Hoff         2    5

so now each person’s friends are linked to their friends – this is the 2nd degree, right?

finally, let’s add the last join –

SELECT users.userId
     , users.fullName   
     , k1.who
     , knows.who
     , employees.companyid
  FROM users
INNER
  JOIN knows AS k1 
    ON users.userId=k1.whom
INNER
  JOIN knows 
    ON knows.whom=k1.who
INNER
  JOIN employees 
    ON knows.who=employees.userId  

userId  fullName         who  who  companyid
   1    Michael Miller    2    1    67
   1    Michael Miller    2    4    68
   1    Michael Miller    2    5    67
   1    Michael Miller    4    1    67
   2    Jefrey White      1    4    68
   2    Jefrey White      4    1    67
   4    Linda Jenkinson   1    4    68
   4    Linda Jenkinson   2    1    67
   4    Linda Jenkinson   2    4    68
   4    Linda Jenkinson   2    5    67
   5    Dave Hoff         2    1    67
   5    Dave Hoff         2    4    68
   5    Dave Hoff         2    5    67

okay, now we’re in great shape

let’s see what the WHERE clause says, by applying the WHERE conditions to the data we’ve retrieved

employees.companyId=67 means we filter out a bunch of rows, and this is what we’re left with –

userId  fullName         who  who  companyid
   1    Michael Miller    2    1    67
   1    Michael Miller    2    5    67
   1    Michael Miller    4    1    67
   2    Jefrey White      4    1    67
   4    Linda Jenkinson   2    1    67
   4    Linda Jenkinson   2    5    67
   5    Dave Hoff         2    1    67
   5    Dave Hoff         2    5    67

now let’s apply the second condition

it says remove the rows where the 2nd degree friend is the same as the original person

this leaves us with –

userId  fullName         who  who  companyid
   1    Michael Miller    2    5    67
   2    Jefrey White      4    1    67
   4    Linda Jenkinson   2    1    67
   4    Linda Jenkinson   2    5    67
   5    Dave Hoff         2    1    67

see how that works?

the employees.userId<>users.userId condition actually did apply

now, in your full query, you apply DISTINCT and of course you show only the original user, so this is what you get –

userId  fullName         
   1    Michael Miller   
   2    Jefrey White     
   4    Linda Jenkinson  
   5    Dave Hoff

what was your question again???

ernest1a · July 7, 2010, 11:59am

Thanks you so much for your help. I gave you the wrong version of the last insert. I apology for my mistake. The last insert is the following:

INSERT INTO tablename.employees (userId, companyId) VALUES (‘1’, ‘67’), (‘3’, ‘67’), (‘4’, ‘68’), (‘5’, ‘67’);

And with this data, it returns also userId 1 even if employees.userId<>users.userId is there.

r937 · July 7, 2010, 11:23am

i just tested your query and it does ~not~ return employee 1

ernest1a · July 7, 2010, 8:07am

The following are exact data I use:


CREATE TABLE  `tablename`.`users` (
`userId` INT NULL AUTO_INCREMENT PRIMARY KEY ,
`fullName` VARCHAR( 50 ) NOT NULL
) ENGINE = MYISAM ;

CREATE TABLE  `tablename`.`employees` (
`userId` INT NOT NULL ,
`companyId` INT NOT NULL
) ENGINE = MYISAM ;

CREATE TABLE  `tablename`.`knows` (
`who` INT NOT NULL ,
`whom` INT NOT NULL
) ENGINE = MYISAM ;

INSERT INTO `tablename`.`users` (`userId`, `fullName`) VALUES ('1', 'Michael Miller'), ('2', 'Jefrey White'), ('3', 'Liam Summer'), ('4', 'Linda Jenkinson'), ('5', 'Dave Hoff');

INSERT INTO `tablename`.`knows` (`who`, `whom`) VALUES ('1', '2'), ('2', '1'), ('2', '4'), ('4', '2'), ('2', '5'), ('5', '2'), ('1', '4'), ('4', '1');

INSERT INTO `tablename`.`employees` (`userId`, `companyId`) VALUES ('1', '67'), ('3', '67'), ('68', '4'), ('67', '5');

And this is a query:


SELECT DISTINCT users.userId, users.fullName 
    FROM users 
    LEFT JOIN knows AS k1 ON users.userId=k1.whom 
    LEFT JOIN knows ON knows.whom=k1.who 
    LEFT JOIN employees ON knows.who=employees.userId 
    WHERE employees.companyId=67
AND employees.userId&lt;&gt;users.userId

So the problem is the query returns also userId 1 which shouldn’t as userId 1 matches employees.companyId=67. I am not sure what I did wrong.

r937 · July 6, 2010, 11:14pm

actually, yes it does take action

would you care to produce a sample data set (a few rows from each of the three tables) then we could test it thoroughly

Topic		Replies	Views
Multiple joins for user network Databases	5	429	May 3, 2010
Left join/inner join question Databases	3	639	March 14, 2011
Rows only show for those who are in the campaigns table Databases	3	1048	May 26, 2016
Table Join Databases	5	637	September 20, 2014
Should be a simple query but Databases	3	519	September 23, 2014

Query with left join doesn't recognize <>

Related topics