SQL Query needs to pull a facility based on the supervising facility based on the supervising command

I have two dropdown queries and the main query.  One is for Commands, there are only two A and B with FCRLAS_ID's of 1010004 and 1010005.   Or they can select all and the following dropdown will show all facilities.  But if they select a command, then there is a short list of *-All Facilities.  Then the main query would show results based on the facility selected or all facilities that have the short list of facilities as their supervising facility.  I'm attaching a short query I'm working with to try and get the dropdown for the facilities to show me the list of  the *-All Facilities.  So, right now, if I select a command I get no results on my main query.   I'm also attaching a list of the results I get when I don't try to pick 1 command.  I get a list of everything for both commands.
SupervisingCommandFacility.docx
SherryDeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SherryDeveloperAuthor Commented:
I got the dropdown for the facilities to work.  But now I'm still trying to get the main query to pull records for the *-All Facilities selection or 'All" for all of the *-All facilities.  I'm just getting everything.
Brendt HessSenior DBACommented:
In your sample data, there is only one ORGNM_SHORT_NM value that is not an -All facilities, correct?  Do get only those that are -All, add this to your WHERE clause

    AND ORGNM_SHORT_NM LIKE '%-All F%'

Open in new window

SherryDeveloperAuthor Commented:
Correct.  I'm getting the correct results on the Facilities dropdown now.  My problem now is with the main query.  If a Command is selected and they choose a All or one of the *-All Facilities in the list, I get no results in the Main Query.  I'm going to do a little more research to see if I have to make the facilities selection drilldown a little more in the Main Query.  A little background:  The Command is one that is over a certain number of prisons.  CCCC-All  Facilities means all the facilities within that particular prison.  Attached is the part of the main query where I'm pulling the facilities params.  I've added the first part to try and get the correct facilities first and then pull the rest of the data.
MainQuery---facilities.docx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SherryDeveloperAuthor Commented:
Ok, I got the right facilities coming into the main query.  I added one more query to break the commands down. Thank you for your help.
SherryDeveloperAuthor Commented:
I needed to break down the facilities that were select in the initial parameters so that I could match them up with the facilities that the person was in/assigned to.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.