How to add couple more conditions to the query

Question

Post reply

How to add couple more conditions to the query

Zond Sita

SSC Veteran

Points: 275
More actions
April 4, 2023 at 4:27 am
Go to Answer
#4168760

Good Morning,

Can you please advise how to add these 2 additional filters to the below query, i tried but didnt work.

1)All Colleges under municipal_ID 4269005 were excluded from the output (below query results).
That MUNICIPALITY should still be included but we want to make sure the College ID 88 under 4269005 also gets into the output.
2) College ID 06 under Municipality 25002 needs to be excluded from the output
SELECT DISTINCT
h.MUNICIPAL_ID,
h.COLLEGE_ID,
h.COLLEGE_YEAR,
h.COLLEGE_NAME
FROM [UNSWEDU].[dbo].[COLLEGE_TABLE] h
JOIN [UNSWEDU].[dbo].[MUNICIPALITY] d
ON h.MUNICIPALITY_KEY = d.MUNICIPALITY_KEY
JOIN [UNSWEDU].[dbo].[COLLEGEENROLLMENT_FACT_PAIRS] fep
ON fep.COLLEGE_KEY = h.COLLEGE_KEY
WHERE COLLEGE_ID NOT IN ( '93', '97', '99' )
---AND (COLLEGE_ID <> '98' AND D.MUNICIPAL_ID <> '49005')
AND d.MUNICIPAL_ID <> '36001'
---AND d.MUNICIPAL_ID <> '00003'
AND d.DISTRICT_TYPE_CD = '10'
AND (SUBSTRING(fep.SERVING_EDUCATIONAL_ENTITY_ID,1,2) <> '99' OR COLLEGE_ID = '97' )
---AND h.MUNICIPAL_ID = '99005' AND COLLEGE_ID = '97'
order by h.COLLEGE_YEAR, h.MUNICIPAL_ID, h.COLLEGE_ID

Thanks
Asita

Dennis Jensen

SSChasing Mays

Points: 604
More actions
April 4, 2023 at 2:32 pm
Answer
#4168995
In Steve Collins solution the final OR negates all the ANDs which I do not think is what the OP wanted so just in case here is an alternate solution.
Further I want to point out that in the original query there is an issue. In the first line you say WHERE CollegeId <> '97' but then in OR clause you say where CollegeId = '97' the first exclusion will eliminate all '97' so that aspect of the OR will never happen and that line really reads as follows:
```
AND ( SUBSTRING( [cfp].[SERVING_EDUCATIONAL_ENTITY_ID], 1, 2 ) <> '99' OR FALSE )
```
Okay I was able to add the CollegeId '06' gets excluded but only if it is within MunicipalId '25002'
However, as for including all MunicipalId '4269005' is an issue because I do not see anywhere in the query where you are specifically excluding it thus it should be included already. Further there is nothing that specifically excludes CollegeId '88' so it also should be included already. So since they are getting excluded that means one of your other exclusions is doing it and you will need to examine them in order to fix that issue. Basically you will need to dig into your data and figure out the why.
```
SELECT DISTINCT
       [ct].[MUNICIPAL_ID]
      ,[ct].[COLLEGE_ID]
      ,[ct].[COLLEGE_YEAR]
      ,[ct].[COLLEGE_NAME]
  FROM [UNSWEDU].[dbo].[COLLEGE_TABLE]                     AS [ct]
------
 INNER JOIN [UNSWEDU].[dbo].[MUNICIPALITY]                 AS [m]
    ON [m].[MUNICIPALITY_KEY] = [ct].[MUNICIPALITY_KEY]
------
 INNER jOIN [UNSWEDU].[dbo].[COLLEGEENROLLMENT_FACT_PAIRS] AS [cfp]
    ON [cfp].[COLLEGE_KEY] = [ct].[COLLEGE_KEY]
------
 WHERE [ct].[COLLEGE_ID] NOT IN ( '93', '97', '99' )
   AND [m].[MUNICIPAL_ID]    <> '36001'
   AND [m].[DISTRICT_TYPE_CD] = '10'
   AND ( SUBSTRING( [cfp].[SERVING_EDUCATIONAL_ENTITY_ID], 1, 2 ) <> '99' OR
         COLLEGE_ID = '97'
       )
   AND NOT ( [m].[MUNICIPAL_ID] = '25002' AND [ct].[COLLEGE_ID] = '06' )
 ORDER BY [ct].[COLLEGE_YEAR], [ct].[MUNICIPAL_ID], [ct].[COLLEGE_ID]
```

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

Maybe 3 WHERE clause conditions could be sufficient (A and B or C):

A) the existing criteria

B) NOT College ID 06 under Municipality 25002

C) municipal_ID equals 4269005 (which would be inclusive of all College ID's including 88)

select distinct h.municipal_id, h.college_id, h.college_year, h.college_name
from [unswedu].[dbo].[college_table] h
     join [unswedu].[dbo].[municipality] d on h.municipality_key = d.municipality_key
     join [unswedu].[dbo].[collegeenrollment_fact_pairs] fep on fep.college_key = h.college_key
where (college_id not in ( '93', '97', '99' )
       and d.municipal_id <> '36001'
       and d.district_type_cd = '10'
       and (substring(fep.serving_educational_entity_id,1,2) <> '99' or college_id = '97' )) /*The current criteria*/
       and not (college_id='06' and h.municipal_id = '25002') /*College ID 06 under Municipality 25002 are excluded*/
       or (municipal_ID='4269005') /*municipal_ID 4269005 (inclusive of College ID 88) should still be included*/order by h.college_year, h.municipal_id, h.college_id;

In a query like this the word DISTINCT is a warning sign which suggests the JOIN conditions are not sufficient. Maybe switch the query to GROUP BY and count the rows. Then look into what's being returned by the JOINs

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Dennis Jensen SSChasing Mays Points: 604 More actions · Answer 2

Redoing noticed something else -- would just delete but do not know how

This reply was modified 1 year, 7 months ago by Dennis Jensen.
This reply was modified 1 year, 7 months ago by Dennis Jensen.

Zond Sita SSC Veteran Points: 275 More actions · Answer 3

Zond Sita

SSC Veteran

Points: 275

April 4, 2023 at 4:29 pm

#4169033

Thank you both. Appreciate your help

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

It'll be interesting to see what kind of Execution Plan is built from all that. The use of DISTINCT also suggests that you may have at least 1 many-to-many return (aka... cross or triangular join).

It may be better (possibly, much better) to find the "gazintas" separately and then join on those rather using all the <> and substring criteria in a Divide'n'Conquer fashion.

With that, I'll suggest that you post the "Actual Execution Plan" for folks to take a look at.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dennis Jensen SSChasing Mays Points: 604 More actions · Answer 5

Yeah I am sure there could be a few ways to alter that SP to a bit more friendly I mean with the OR clause being not an OR clause due to the previous exclusion that check could be move up to the INNER JOIN as such:

------
INNER jOIN [UNSWEDU].[dbo].[COLLEGEENROLLMENT_FACT_PAIRS] AS [cfp]
   ON [cfp].[COLLEGE_KEY] = [ct].[COLLEGE_KEY]
  AND SUBSTRING( [cfp].[SERVING_EDUCATIONAL_ENTITY_ID], 1, 2 ) <> '99'

And a similar thing could be done with the INNER JOIN as follows:

------
 INNER JOIN [UNSWEDU].[dbo].[MUNICIPALITY] AS [m]
    ON [m].[MUNICIPALITY_KEY] = [ct].[MUNICIPALITY_KEY]
   AND [m].[MUNICIPAL_ID]    <> '36001'
   AND [m].[DISTRICT_TYPE_CD] = '10'

Not sure if doing that would improve anything but it does move the checks closer to where they should be applied and that cannot be a bad thing.

I also find it kind of odd that the the Municipal_Id is in both the College_Table and Municipality table when those two tables are joined on the Municipality_Key and the exclusion of the Municipal_Id is handled against the Municipality table but the Municipal_Id is being pulled from the College_Table. I would be kind of interested in seeing the table layouts, I get the feeling there are some basic issues that underlay this query that might need to be addressed as well and could part of the reason they have to use the DISTINCT.