Solved

Get String from SQL Query

Posted on 2013-01-12
7
516 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

777 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