current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 2 down vote favorite

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
share|improve this question
    
will this ever return a null for the Structure column? –  Malachi Jun 30 at 14:46
    
Yes it can do, sorry didn't see your answer. I'll check it now –  Pete Jun 30 at 15:08
    
if it will return null then I need to change my answer slightly, I was making some assumptions when I wrote the query. –  Malachi Jun 30 at 15:09
    
does either the FIRSTNAME or SURNAME return null on occasion as well? –  Malachi Jun 30 at 15:21
1  
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 –  Pete Jun 30 at 15:56

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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 –  Pete Jun 30 at 15:14
    
let me edit the query –  Malachi Jun 30 at 15:15
    
that should do it. –  Malachi Jun 30 at 15:17
    
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 –  Pete Jun 30 at 15:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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