Solved

Get String from SQL Query

Posted on 2013-01-12
7
520 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

Suggested Solutions

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. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 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