Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Get String from SQL Query

Posted on 2013-01-12
7
Medium Priority
?
524 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 23

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 23

Assisted Solution

by:Steve Wales
Steve Wales earned 2000 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 23

Accepted Solution

by:
Steve Wales earned 2000 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

CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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 the fundamental information of how to create a table.

704 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