Solved

Get String from SQL Query

Posted on 2013-01-12
7
518 Views
Last Modified: 2013-01-14
I have a query that will get one to many records.  I then need get those record ID's into a string to match in my next query.

        <cfquery name="local.chkHCO" datasource="#mydns#" username="#myusrnm#" password="#mypass#">
            SELECT fac_id
            FROM facilitytable
            WHERE fac_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.fac_id#" /> and fac_typ_catg_c = 'AS'
        </cfquery>

This query may get 3 facilities with ID's of 34, 50, 89.  The next query needs to get all the users from each of those facilities.

        <cfquery name="local.chkHCO" datasource="#mydns#" username="#myusrnm#" password="#mypass#">
            SELECT usr_acct_id FROM usr_acct WHERE fac_id IN (THE STRING FROM MY ABOVE QUERY)
        </cfquery>
0
Comment
Question by:Lee R Liddick Jr
  • 4
  • 3
7 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38771299
Why not combine them into one?

SELECT usr_acct_id 
FROM usr_acct 
WHERE fac_id IN
( SELECT fac_id
            FROM facilitytable
            WHERE fac_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.fac_id#" /> and fac_typ_catg_c = 'AS')

Open in new window


(assuming your cfquery construct supports a select statement build like that, I am not familiar with this construct)

In native SQL that's what the query would like in

select column from table where other_column in (select other column from other_table where some_condition_is_true).
0
 

Author Comment

by:Lee R Liddick Jr
ID: 38771307
Because I can't combine them.  The first query is getting the users at that facility.  Two of my other tables that hold information pertaining to the users do not have facility id's.  So I need to get the string of user_id's so that I may update them in another query.

Basically, if the facility is being deactivated or no longer a client, I am deactivating all the users in the system that was affiliated with that facility.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 38773075
I've requested that this question be deleted for the following reason:

I have responded to the expert with no other responses.  I did not find a solution.  I will just look elsewhere or repost the question in another fashion.
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 500 total points
ID: 38773076
I think it may be a little early to close this one yet.

I don't know if your other action is an update or a delete, let's assume for the sake of argument that it's an update and you're setting some field to inactivate the users found.

If you need the list of users so you can act on them you can still use the construct like this:

UPDATE the_user_table
SET usr_active_flag = 'N'
WHERE usr_acct_id in 
 (SELECT usr_acct_id 
 FROM usr_acct  
 WHERE fac_id IN
  (SELECT fac_id
   FROM facilitytable
   WHERE fac_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.fac_id#" /> and fac_typ_catg_c = 'AS')

Open in new window

                                           
Does that help you out any ?
0
 

Author Comment

by:Lee R Liddick Jr
ID: 38773142
This does help, however, my other issue still remains, which I didn't spell out (one of the reasons why I was going to just delete the question and start over).  In any case, I have two levels of facilities:  Owner or Corporate level and then just the Facility level.  Both types are in the same table, and I have an affiliation table that we query in order to get all the facilities under one particular owner.

facility_table
fac_id           typ_cat           fac_nm
2                       AS               Jackson Corporation
3                       MS              Miami Location
4                       MS              Dallas Location

So the AS category is the owner and the MS are the facility locations.

facility_affiliation_table
afl_frm_fac_id           afl_to_fac_id    
        3                                 2
        4                                 2

So in the facilitye affiliaton table it says that fac_id 3 and 4 belong to fac_id 2.

Now my problem is, if we Inactivate an Owner (in this case fac_id #2)  I need to UPDATE my contact tables to remove any user with a fac_id of 2, 3, or 4.

That's why I was trying to create the array.  Hopefully that makes sense.
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38773190
Unless you're taking the results of a query into your code and manipulating them that way, SQL doesn't really handle "arrays" or "comma separated string" or whatever you want to call them as an entity it can query on.

If you think of the return from any query as just another table (because in short, that's what a result set is, it's just a representation of a table, then hopefully you see why what you were trying to do doesn't work.

You would need to build on the query from a couple of answers up to be something like:

WHERE fac_id IN
  ( [existing subquery])
OR fac_id in
 (select afl_frm_fac_id from facility_affiliation_table where afl_to_fac_id = <parameter you get from your code>)

Something like that might do the trick - but it's hard to say without a better understanding of your data structures and requirements.
0
 

Author Closing Comment

by:Lee R Liddick Jr
ID: 38776623
That was exactly what I needed.  Thank you so much for the explanation along with the examples you provided.  The extra was greatly appreciated as well.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question