CODEWITHSUNDEEP
sqlsql-servert-sql
Michael A
Michael A

Reputation: 9910

How do I do this in a query (example provided)?

I'm some what new to SQL being exposed to it in my current role.

How do I write the following query so it can work?

select *
from property.lease_period lp
where lp.lease_current_stop_date < getdate() and (lp.lease_status = 'Active' or lp.lease_status = 'Overholding')
and lp.period_id = 263      --- Period ID
and lp.building_id = 40000  --- Building ID
and not (SELECT *
            FROM property.lease_period lp
            inner join lease_deal.lease ld on lp.suite_id = ld.tenancy_reference
            where lp.lease_current_stop_date < getdate() and (lp.lease_status = 'Active' or lp.lease_status = 'Overholding')
            and lp.period_id = 263
            and lp.building_id = 40000)

Essentially I want to show the results from:

select *
from property.lease_period lp
where lp.lease_current_stop_date < getdate() and (lp.lease_status = 'Active' or lp.lease_status = 'Overholding')
and lp.period_id = 263      --- Period ID
and lp.building_id = 40000  --- Building ID

without including results that match:

SELECT *
FROM property.lease_period lp
inner join lease_deal.lease ld on lp.suite_id = ld.tenancy_reference
where lp.lease_current_stop_date < getdate() and (lp.lease_status = 'Active' or lp.lease_status = 'Overholding')
and lp.period_id = 263
and lp.building_id = 40000

Sorry for the basic question! Also, Any other tips beyond this for formatting my SQL better would be greatly appreciated!

Edit:

I believe this may be the solution to what I'm looking for:

select
    *
from
    property.lease_period lp
where 
    lp.lease_current_stop_date < getdate() and (lp.lease_status = 'Active' or lp.lease_status = 'Overholding')
    and lp.period_id = 263      --- Period ID
    and lp.building_id = 40000  --- Building ID
    and not exists
    (
        select 1
        from lease_deal.lease
        where lp.suite_id = tenancy_reference
    )
order by period_id desc

Definitely interested in hearing better methods for writing this!

Upvotes: 0

Views: 64

Answers (2)

phatfingers
phatfingers

Reputation: 10250

You can left join to your exception table and only accept the null matches from that join.

select lp.*
from property.lease_period lp
left join lease_deal.lease ld on lp.suite_id = ld.tenancy_reference
where lp.lease_current_stop_date < getdate()
and (lp.lease_status = 'Active' or lp.lease_status = 'Overholding')
and lp.period_id = 263      --- Period ID
and lp.building_id = 40000  --- Building ID
and ld.tenancy_reference is null

Upvotes: 1

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107566

This might be a simpler way of handling it (along with my personal formatting preference):

SELECT 
    *
FROM 
    property.lease_period lp
WHERE
    lp.lease_current_stop_date < GETDATE() 
    AND
    (lp.lease_status = 'Active' or lp.lease_status = 'Overholding')
    AND 
    lp.period_id = 263      --- Period ID
    AND
    lp.building_id = 40000  --- Building ID
    AND
    lp.suite_id NOT IN (SELECT tenancy_reference FROM lease_deal.lease)

Upvotes: 1

Related Questions