CODEWITHSUNDEEP
mysqlmariadb
KingMalaka
KingMalaka

Reputation: 23

discard SQL result based on the value of another table

I'm very lost when it comes to nested sql statements.

I'm currently building a database to manage executive suites and I'd like to be able to find vacant suites by comparing what I have leased vs the total suites I have available.

This is the best I've been able to come up with:

SELECT * FROM `executiveSuites` 
LEFT JOIN `executiveLease` 
ON executiveLease.suiteNumber = executiveSuites.SuiteNumber 
AND executiveLease.Property = executiveSuites.BuildingID 
WHERE NOT EXISTS 
( SELECT * FROM `executiveLease` WHERE executiveLease.status = 1 )

the expected result would be that any suite that doesn't have a lease attached with a status of 1 would show up but what I'm getting is a blank list.

I'm self taught and don't know what I don't know, so any help or at the least a point in the right direction is very much appreciated.

Upvotes: 0

Views: 60

Answers (1)

Akina
Akina

Reputation: 42728

Looks like you need in

SELECT * 
FROM `executiveSuites` es
WHERE NOT EXISTS ( SELECT NULL
                   FROM `executiveLease` el
                   WHERE el.suiteNumber = es.SuiteNumber 
                     AND el.Property = es.BuildingID 
                     AND el.status = 1 )

or

SELECT * 
FROM `executiveSuites` es
LEFT JOIN `executiveLease` el ON el.suiteNumber = es.SuiteNumber 
                             AND el.Property = es.BuildingID
WHERE NOT EXISTS ( SELECT NULL 
                   FROM `executiveLease` el_s
                   WHERE el_s.suiteNumber = es.SuiteNumber 
                     AND el_s.Property = es.BuildingID 
                     AND el_s.status = 1 )

Upvotes: 1

Related Questions