CODEWITHSUNDEEP
phpmysql
Roaster bhusri
Roaster bhusri

Reputation: 53

left join two tables such that two columns of table 2 does not exist in table1

I have two tables table1(userid,regid) and table2(userid,hostuserid,status) where either userid or hostuserid equals myuserid(already contained in a variable $userid).I want to find all rows in table1 such that table1.userid!=table2.userid AND table1.userid!=table2.userid.

table1
--------
userid regid
 1     gbjnknnk
 2     bvgcghb
 3     bjbnjb

 table2
 -------
 userid hostuserid 
  1       5
  5       2

  $userid=5

query should return only one row=> 3 bjbnjb how to implement the same. what i had tried

SELECT * FROM table1 JOIN table2 WHERE (table1.userid!=table2.userid AND 
table2.hostuserid=$userid) AND (table1.userid!=table2.hostuserid AND 
table2.userid=$userid)

Upvotes: 1

Views: 44

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

You could use a not in on a subselect with union 

select userid 
from table1 
where user1 not in ( 
      select userid 
      from table2 
      union 
      select 
      hostuserid from table2
)

Upvotes: 0

Related Questions