Need help with subquery.

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
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SimonConnect With a Mentor Commented:
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
 
PortletPaulConnect With a Mentor freelancerCommented:
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
 
brgdotnetcontractorAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.