Selecting within a selection using SQL from a single table

Question

I have to make a query inside another query in order to find entries in a table that have characteristics but not others. The characteristics are derived from a connection to another table.

Basically, I have a plans table and a parcels table. I need to find the plans that relate to both (building strata, bareland strata, common ownership) and (road, subdivision, park, interest). These plans should contain entries in one list, but not both.

Here is what I have so far.

SELECT * 
FROM parcelfabric_plans 
WHERE 
(name in 
    (select pl.name from parcelfabric_parcels p inner join 
     parcelfabric_plans pl on p.planid = pl.objectid
        WHERE
            p.parcelclass IN ( 'ROAD', 'SUBDIVISION', 'PARK', 'INTEREST')))

This is the first query, which gets all the plans that have parcels related to them in this list. How do I query this selection to get plans within this selection that are also related to the second list (subdivisions, interests, roads, parks)?

This query returns 268983 results of plans. Of these results, I would like to be able to query them and get the number of plans that are also related to subdivisions, interests, roads, parks.

Please [Edit] your question to add some sample data and the desired result. — PM 77-1, Jul 08 '22 at 23:00
even after reading it multiple times it is hard to understand what ou are searching for, my gues you want a comparion of a column from the outer FROM — nbk, Jul 08 '22 at 23:19

shawnt00 · Answer 1 · 2022-07-09T00:10:31.247

This would require elements from both lists:

select pl.name
from parcelfabric_plans pl
where exists (
    select 1 from parcelfabric_parcels p 
    where p.planid = pl.objectid
        and p.parcelclass in ('ROAD', 'SUBDIVISION', 'PARK', 'INTEREST')
) and exists (
    select 1 from parcelfabric_parcels p 
    where p.planid = pl.objectid
        and p.parcelclass in (<list 2>)
)

I'm not clear about the requirement though. If you want them to be mutually exclusive then I think this is a better idea:

with data as (
    select p.planid,
        count(case when p.parcelclass in
            ('ROAD', 'SUBDIVISION', 'PARK', 'INTEREST') then 1 end) as cnt1,
        count(case when p.parcelclass in
            (<list 2>) then 1 end) as cnt2
    from parcelfabric_plans pl inner join parcelfabric_parcels p
        on p.planid = pl.objectid
    -- possible optimization
    /* where p.parcelclass in (<combined list>) */
    group by p.planid
)
select * from data
where cnt1 > 0 and cnt2 = 0 or cnt1 = 0 and cnt2 > 0;

Thank you for your response, I really appreciate it. I figured out a different way and it seems to work the way I want it to, though it is much less elegant than your answer. — ALexander2104, Jul 09 '22 at 16:29

score 0 · Answer 2 · answered Jul 09 '22 at 16:32

I would like to thank everyone for their comments and answers. I figured out a solution, though it is quite clunky. But at least it works.

    SELECT * 
FROM pmbcprod.pmbcowner.ParcelFabric_Plans 

WHERE 
(name in
    (select pl.name from parcelfabric_parcels p inner join parcelfabric_plans pl on p.planid = pl.objectid
        WHERE
            p.parcelclass IN ('ROAD','INTEREST','SUBDIVISION','PARK') 
)and name in
    (select pl.name from parcelfabric_parcels p inner join parcelfabric_plans pl on p.planid = pl.objectid
        WHERE
            p.parcelclass IN ('BUILDING STRATA','COMMON OWNERSHIP','BARE LAND STRATA')
)
)

What I was after was simpler than I thought, I just needed to wrap my head around the structure. It's basically a nested query (subquery?). The inner query is made, then the next one is formed around it.

Again, thank you and it is much appreciated. Cheers to all.

If `objectid` is your primary key then you ought to be matching on that otherwise there's potential for name collisions/overlap/bleed that give undesired results. If the tables are big then this is likely slower than using correlation. — shawnt00, Jul 09 '22 at 20:16

Selecting within a selection using SQL from a single table

2 Answers2