Solved

Need help with subquery.

Posted on 2015-02-12
3
84 Views
Last Modified: 2015-02-16
Please refer to my query below numbered with lines 1 through 16.
On line 12, I need to go to another table "EMPLOYEES" to get the value for the
SECA_ASSOC_NUM. So how can I modify line 12 below to get the SECA_ASSOC_NUM ?
I will also need to pass in the PSUS_USER_ID. Can someone help me out? I did try
something (See very bottom of this post) but the query I rewrote for line 12 is not correct

EMPLOYEES table, is defined as follows.

SSN            FNAME         LNAME           SECA_ASSOC_NUM   PSUS_USER_ID
varchar(9) varchar(50)  varchar(50)   varchar(50)                 varchar(8)



1  SELECT * INTO #TMP_PR FROM smts..SMTS_BLLD_USER_DETAILS
2  DELETE T FROM #TMP_PRT
3  INNER JOIN SMT_ADMN_PSUS_PR_STE_USERS S
4  ON T.SECA_ASSOC__NUM = S.SECA_ASSOC_NUM
5  AND T.PSUS_USER_ID = S.PSUS_USER_ID
6  INSERT INTO SMT_ADMN_PSUS_PR_STE_USERS(
7  SECA_ASSOC_NUM,
8  PSUS_USER_ID,
9  PSUS_ACTIVE
10  )
11  SELECT
12  SECA_ASSOC_NUM, -- Not in temp table. I have to go to another table named EMPLOYEES to get the SECA_ASSOC_NUM
13  PSUS_USER_ID,
14  PSUS_ACTIVE  FROM
15  WHERE PSUS_USER_ID NOT IN(SELECT FUUP_USUS_ID_OLD FROM SMT_FCTS_FUUP_UPD_USUS_ID)
16  FROM SMT_FCTS_FUUP_UPD_USUS_ID

I thought of something like this for line 12, but it won't work. See my line 12 below
11  SELECT
12. SECA_ASSOC_NUM = (SELECT SECA_ASSOC_NUM FROM EMPLOYEE where EMPLOYEE.PSUS_USER_ID = PSUS_USER_ID) -- This does not work. Need expert help
13  PSUS_USER_ID,
14  PSUS_ACTIVE  FROM
0
Comment
Question by:brgdotnet
3 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 300 total points
ID: 40607377
Very nearly there... Try this as your line 12

(SELECT SECA_ASSOC_NUM FROM EMPLOYEE where EMPLOYEE.PSUS_USER_ID = PSUS_USER_ID) as SECA_ASSOC_NUM

There is also something missing from line 14

14  PSUS_ACTIVE  FROM [TableOrViewNameHere]

And line 16 should not be there (FROM after WHERE clause)
FROM SMT_FCTS_FUUP_UPD_USUS_ID

But maybe you know about that already?
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 200 total points
ID: 40607429
I'm sorry but I don't see the lines 1 to 16 forming a sort of cohesive query without more input from you.

e.g is #TMP_PR (line 1) supposed to be #TMP_PRT (line 2)

I think you are trying to create one temp table (#TMP_PRT)
Then compare it to a source table (SMT_FCTS_FUUP_UPD_USUS_ID)
so that you can insert into another source table  (SMT_ADMN_PSUS_PR_STE_USERS)

I think this is what it should look like
line 16 below is a guess:
SELECT
      *
INTO #TMP_PRT
FROM smts..SMTS_BLLD_USER_DETAILS T
      INNER JOIN SMT_ADMN_PSUS_PR_STE_USERS S ON T.SECA_ASSOC__NUM = S.SECA_ASSOC_NUM
      AND T.PSUS_USER_ID = S.PSUS_USER_ID
;


-- INSERT INTO SMT_ADMN_PSUS_PR_STE_USERS (SECA_ASSOC_NUM, PSUS_USER_ID, PSUS_ACTIVE)
      SELECT
            EMPLOYEE.SECA_ASSOC_NUM -- Not in temp table. I have to go to another table named EMPLOYEES to get the SECA_ASSOC_NUM
          , #TMP_PRT.PSUS_USER_ID
          , #TMP_PRT.PSUS_ACTIVE
          
      FROM #TMP_PRT --<< this was a guess!!!!
      INNER JOIN EMPLOYEE ON EMPLOYEE.PSUS_USER_ID = #TMP_PRT.PSUS_USER_ID
      WHERE PSUS_USER_ID NOT IN (
                  SELECT
                        FUUP_USUS_ID_OLD
                  FROM SMT_FCTS_FUUP_UPD_USUS_ID
            )
;

Open in new window


Notes:
1. I have commented out line 10 deliberately so that you can run this as a test before doing any inserts

2. I have assume INNER JOIN to the employee table is appropriate, I do not know this for certain
    for example I do not know if this could create more rows than is desired (or less rows for that matter)

3. Creating a temp table by "select *" means you are potentially moving much more data than you need.
    You really should specify the columns you need, rather than just asking for everything that is available.

4. A different way to get EMPLOYEE.SECA_ASSOC_NUM would be to make that join to employees earlier
      so that the temp table IS populated with that column.

5. at least 2 other techniques could be used for EMPLOYEE.SECA_ASSOC_NUM
    a) cross apply
    b) correlated subquery
    if joining does not work as I expect it should
    but joining is the preferred approach
0
 
LVL 2

Author Closing Comment

by:brgdotnet
ID: 40612390
Thanks so much experts. I think Simons answer in this case is what I need. I want to thank PortletPaul for also being awesome.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 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