Get String from SQL Query

Posted on 2013-01-12
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'

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)
Question by:Lee R Liddick Jr
  • 4
  • 3
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).

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.

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.
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

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 ?

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.

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.

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.
LVL 22

Accepted Solution

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.

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.

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 27
MSSQL 2014 Query Synthax 8 38
SQL Server 2012 Row Selection 2 29
Increasing Identity length in sql server 4 17
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now