Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need help with subquery.

Posted on 2015-02-12
3
Medium Priority
?
101 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 1200 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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 800 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

773 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