How to query records one table by checking record field in another?-CodePudding

I have two tables:

TableOne

id	name	orgId
id-1	One	org-1
id-2	Two	org-1
id-3	Three	org-1
id-4	Four	org-2

TableTwo

id	status
id-1	Ok
diff-id-1	Ok
diff-id-2	Ok

How to write a query to retrieve records from TableOne where orgId=org-1 and userId is not present in TableTwo?

In JS I would write a if as a two for loops.

I wrote the beginning of a statement:

SELECT * from schema.TableOne
WHERE orgId = 'org-1'

But I have no idea how to check TableOne id to TableTwo id.

The result should be:

id	name	orgId
id-2	Two	org-1
id-3	Three	org-1

CodePudding user response：

You can use exists with a subquery:

select t1.* from tableone t1 where t1.orgid='org-1' 
    and not exists (select 1 from tabletwo t2 where t2.id = t1.id)

CodePudding user response：

You can do an left outer join on the user id with table2two. The ON clause is used for the join. then add a where clause where you filter for for orgid = "org-1" , and you filter out any rows where there is amatch in TableTwo, that is done by only inlcuding row where TableTwo.Id is null

SELECT
    t1.*
FROM TableOne t1
LEFT JOIN TableTwo t2
    ON t1.Id = t2.Id
WHERE t1.orgid = 'org-1'
AND t2.Id IS NULL

CodePudding user response：

You can use the below query

select * from #TableOne where orgId='org-1' and ID not in (select ID from #TableTwo)

enter image description here