Solved

Need help with subquery.

Posted on 2015-02-12
3
95 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
[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
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 49

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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…

617 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