Selecting within a selection using SQL from a single table-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

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.