2
\$\begingroup\$

I was wondering if there is a better way to do the nested selects so that the firstname and surname is only doing one select (but still returning 2 fields) - something like a join with a where clause in it that only returns the first matching object.

SELECT
    w.[RECID],
    w.[PERSONNELNUMBER],
    (
        SELECT TOP 1
            [FIRSTNAME]
        FROM [dbo].[DIRPERSONNAME] AS d
        WHERE d.PERSON = w.PERSON
    ) AS FirstName,
    (
        SELECT TOP 1
            LASTNAME
        FROM [dbo].[DIRPERSONNAME] AS d
        WHERE d.PERSON = w.PERSON
    ) AS Surname,
    u.[USER_] AS UserName,
    l.[LOCATOR] AS Email,
    (
        SELECT TOP 1
            ed.MARHRSTRUCTUREID AS Structure
        FROM [dbo].[HCMEMPLOYMENT] AS e
        INNER JOIN [dbo].[MARHCMEMPDETAILS] AS ed ON e.RECID = ed.EMPLOYMENT
        WHERE e.Worker = w.RECID
    ) AS Structure
FROM
    [dbo].[DIRPARTYTABLE] AS p
    INNER JOIN [dbo].[HCMWORKER] AS w ON w.PERSON = p.RECID
    LEFT JOIN [dbo].[DIRPERSONUSER] AS u ON w.PERSON = u.PERSONPARTY
    LEFT JOIN [dbo].[LOGISTICSELECTRONICADDRESS] AS l ON l.RECID = p.PRIMARYCONTACTEMAIL
\$\endgroup\$
7
  • \$\begingroup\$ will this ever return a null for the Structure column? \$\endgroup\$ Commented Jun 30, 2015 at 14:46
  • \$\begingroup\$ Yes it can do, sorry didn't see your answer. I'll check it now \$\endgroup\$ Commented Jun 30, 2015 at 15:08
  • \$\begingroup\$ if it will return null then I need to change my answer slightly, I was making some assumptions when I wrote the query. \$\endgroup\$ Commented Jun 30, 2015 at 15:09
  • \$\begingroup\$ does either the FIRSTNAME or SURNAME return null on occasion as well? \$\endgroup\$ Commented Jun 30, 2015 at 15:21
  • 1
    \$\begingroup\$ I'll have a look at doing this tomorrow - finished for the day now and I want to make the most of the sunshine! thanks for your time \$\endgroup\$ Commented Jun 30, 2015 at 15:56

1 Answer 1

Reset to default
1
\$\begingroup\$

You don't need those nested select statements at all, you just need to join to the DIRPERSONNAME table on the Identifiers that you are already using from the other tables.

I also included HCMEMPLOYMENT and MARHCMEMPDETAILS linking e.Worker to w.RECID

This should be much more efficient.

SELECT w.[RECID]
    , w.[PERSONNELNUMBER]
    , d.[FIRSTNAME]
    , d.[SURNAME]
    , u.[USER_] AS UserName
    , l.[LOCATOR] AS Email
    , ed.MARHRSTRUCTUREID AS Structure
    FROM [dbo].[DIRPARTYTABLE] AS p
        INNER JOIN [dbo].[HCMWORKER] AS w ON w.PERSON = p.RECID
        LEFT JOIN [dbo].[DIRPERSONNAME] AS d ON d.PERSON = w.PERSON
        LEFT JOIN [dbo].[HCMEMPLOYMENT] AS e ON e.Worker = w.RECID
        INNER JOIN [dbo].[MARHCMEMPDETAILS] AS ed ON e.RECID = ed.EMPLOYMENT
        LEFT JOIN [dbo].[DIRPERSONUSER] AS u ON w.PERSON = u.PERSONPARTY
        LEFT JOIN [dbo].[LOGISTICSELECTRONICADDRESS] AS l ON l.RECID = p.PRIMARYCONTACTEMAIL

My Guess is that the First and Surname can both be empty so I made the DIRPERSONNAME table a LEFT JOIN as well.

\$\endgroup\$
4
  • \$\begingroup\$ Just checked and this seems to bring back less results - I think it may be due to the inner joins as structure can be null \$\endgroup\$ Commented Jun 30, 2015 at 15:14
  • \$\begingroup\$ let me edit the query \$\endgroup\$ Commented Jun 30, 2015 at 15:15
  • \$\begingroup\$ that should do it. \$\endgroup\$ Commented Jun 30, 2015 at 15:17
  • \$\begingroup\$ Yeah, that's what I started off with but because there can be multiple entries for the same person in the DIRPERSONNAME table, it gives me duplicated content. Unfortunately, I can't use group as some of the fields are slightly different, which is why I went with the top 1 nested select statement \$\endgroup\$ Commented Jun 30, 2015 at 15:38

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.