CODEWITHSUNDEEP
mysqlsql
TheBiker
TheBiker

Reputation: 183

SQL Where clause to query another table

I have two tables and I want to use a where clause that queries another table. Here are my two tables:

TABLE USERS

User_ID  Organization_ID
User1    1
User2    2
User3    3

TABLE POLICIES

User_ID  Policy_ID
User1     5
User2     5
User3     5
User1     6
User2     7
User3     8

I want to do the following:

Select *
from POLICIES
WHERE Policy_id=5 AND TABLE_USERS.Organization_ID DOES NOT = 1

How do I write this? Thanks

Upvotes: 0

Views: 609

Answers (5)

ShafirR
ShafirR

Reputation: 24

Give a try on this!

select P.User_ID,P.Policy_ID,U.Organization_ID from POLICIES P
left join TABLE_USERS U ON P.User_ID = U.User_ID
--METHORD 1
--where P.Policy_ID = '5' AND U.Organization_ID <> ='1'

--METHORD 2 
where P.Policy_ID = '5' AND U.Organization_ID NOT IN (SELECT U.Organization_ID FROM POLICIES WHERE U.Organization_ID ='1')

Upvotes: 1

Daniel Routley
Daniel Routley

Reputation: 38

SELECT * FROM policies P
JOIN table_users tu  ON tu.user_ID = p.user_ID
WHERE p.policy_ID = 5 AND tu.organisation_ID != 1

Give this a try

Upvotes: 1

Dave Morrison
Dave Morrison

Reputation: 179

You need to inner join the table users on user id.

Like this

SELECT * FROM POLICIES P
INNER JOIN TABLE_USERS TU ON P.USER_ID = TU.USER_ID 
WHERE P.POLICY_ID = 5 AND TU.ORGANIZATION_ID <> 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Use NOT EXISTS:

SELECT p.*
FROM POLICIES p
WHERE p.Policy_id = 5 AND
      NOT EXISTS (SELECT 1
                  FROM TABLE_USERS u
                  WHERE u.Organization_ID = 1 AND
                        u.User_id = p.User_Id
                 );

Upvotes: 0

apomene
apomene

Reputation: 14389

You will need an INNER JOIN. Try like:

Select p.* from POLICIES p INNER JOIN TABLE_USERS u on p.USER_ID = u.User_ID 
WHERE p. Policy_id=5 AND u.Organization_ID <> 1

Upvotes: 0

Related Questions