CODEWITHSUNDEEP
sqldatabasepostgresql
Karen
Karen

Reputation: 1429

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

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

Upvotes: 1

Views: 33

Answers (3)

Akshay Shinde
Akshay Shinde

Reputation: 1

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

Upvotes: 0

Søren Kongstad
Søren Kongstad

Reputation: 1440

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

Upvotes: 0

Ajax1234
Ajax1234

Reputation: 71451

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)

Upvotes: 1

Related Questions