CODEWITHSUNDEEP
mysqlsqljoin
Chris Farrugia
Chris Farrugia

Reputation: 1046

How to select all records in one table where no records in second table based on criteria

I have a table with subdivisions:

id
subcondoname

I have a second table that has real estate listings that looks like this:

SubdivisionID (this ties back to id on the subdivision table)
inactive
status

I want a query that pulls every subcondoname from the first table where there is no record in the second with inactive = 0 or status = active

Upvotes: 0

Views: 39

Answers (4)

Mokka soru
Mokka soru

Reputation: 513

You have get some idea from following link.

https://dataedo.com/kb/query/sql-server/find-empty-tables-in-database

Upvotes: 0

Ankit Awal
Ankit Awal

Reputation: 11

Adding to above two answers,

NOT EXISTS will run bit faster as compare to LEFT JOIN. Because LEFT JOIN reads data from both the table i.e. scanning and than compares them as per the given logic whereas NOT EXISTS simply do ANTI SEMI JOIN which partially fetches result from Subdivision table.

Upvotes: 1

JIB
JIB

Reputation: 11

Please try the code below

SELECT * FROM subdivision s LEFT JOIN realstate r on s.id = r.subdivisionid WHERE Isnull(r.inactive,0) = 0 OR r.status = 'active'

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272096

A simple NOT EXISTS would do it:

SELECT *
FROM t1
WHERE NOT EXISTS (
    SELECT 1
    FROM t2
    WHERE t2.subdivisionid = t1.id AND (t2.inactive = 0 OR t2.status = 'active')
)

Upvotes: 2

Related Questions