Solved

Need help with subquery.

Posted on 2015-02-12
3
73 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:
SimonAdept 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now