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\$
  • \$\begingroup\$ will this ever return a null for the Structure column? \$\endgroup\$ – Malachi Jun 30 '15 at 14:46
  • \$\begingroup\$ Yes it can do, sorry didn't see your answer. I'll check it now \$\endgroup\$ – Pete Jun 30 '15 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\$ – Malachi Jun 30 '15 at 15:09
  • \$\begingroup\$ does either the FIRSTNAME or SURNAME return null on occasion as well? \$\endgroup\$ – Malachi Jun 30 '15 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\$ – Pete Jun 30 '15 at 15:56
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.

|improve this answer|||||
\$\endgroup\$
  • \$\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\$ – Pete Jun 30 '15 at 15:14
  • \$\begingroup\$ let me edit the query \$\endgroup\$ – Malachi Jun 30 '15 at 15:15
  • \$\begingroup\$ that should do it. \$\endgroup\$ – Malachi Jun 30 '15 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\$ – Pete Jun 30 '15 at 15:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.