web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / SQL query help
Microsoft Dynamics GP (Archived)

SQL query help

(0) ShareShare
ReportReport
Posted on by painterisaac 3,015

I can't figure out why my SQL code isn't working. I am trying to add three tables. Thank you

SELECT    

dbo.RM20101.DOCDATE,

dbo.RM20101.ORTRXAMT,  

dbo.RM20101.DOCNUMBR,

dbo.RM00101.CUSTNAME,

dbo.RM00101.CUSTCLAS,

dbo.RM20101.CHEKNMBR,

dbo.RM00101.SLPRSNID,

dbo.RM20101.CSPORNBR,

dbo.RM00301.SLPRSNFN,

dbo.RM00301.SPRSNSLN

FROM        

dbo.RM00101 with (nolock)

INNER JOIN

dbo.RM20101 with (nolock) ON dbo.RM00101.CUSTNMBR = dbo.RM20101.CUSTNMBR

dbo.RM00301 with (nolock) ON dbo.RM00301.SLPRSNID = dbo.RM20101.SLPRSNID

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at

    SELECT A.DOCDATE,
           A.ORTRXAMT,
           A.DOCNUMBR,
           A.CUSTNMBR,
           B.CUSTNAME,
           B.CUSTCLAS,
           A.CHEKNMBR,
           A.CSPORNBR,
           A.SLPRSNID,
           A.SLPRSNID,
           C.SLPRSNFN,
           C.SPRSNSLN
           FROM RM20101 AS A
    INNER JOIN RM00101 AS B
    ON A.CUSTNMBR = B.CUSTNMBR
    LEFT OUTER JOIN RM00301 AS C
    ON A.SLPRSNID = C.SLPRSNID

  • painterisaac Profile Picture
    painterisaac 3,015 on at

    Hmmm...the salesperson first and last name is coming up blank. Any idea why?

  • Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at

    if there is a salesman Id with blank first and last name, then they are already not filled out at the salesman window. Otherwise, if the salesman id, first and last name are all null, then there is no corresponding salesman for the open document.

  • Verified answer
    MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    There's only one thing wrong with your query (your original question) and that is, you are missing a LEFT OUTER join to your Salesperson Master:

    SELECT    

    RM20101.DOCDATE,

    RM20101.ORTRXAMT,  

    RM20101.DOCNUMBR,

    RM00101.CUSTNAME,

    RM00101.CUSTCLAS,

    RM20101.CHEKNMBR,

    RM00101.SLPRSNID,

    RM20101.CSPORNBR,

    RM00301.SLPRSNFN,

    RM00301.SPRSNSLN

    FROM        

    RM00101 with (nolock)

    INNER JOIN RM20101 with (nolock) ON RM00101.CUSTNMBR = RM20101.CUSTNMBR

    LEFT OUTER JOIN RM00301 with (nolock) ON RM00301.SLPRSNID = RM20101.SLPRSNID

  • Verified answer
    painterisaac Profile Picture
    painterisaac 3,015 on at

    Thank you for help, both of you.

    To get the SalesPerson First and Last name you have to change some things on the left outer join so that it refers to the customer master. Pulling from RM20101 will result in a null value.

    SELECT    

    RM20101.DOCDATE,

    RM20101.ORTRXAMT,  

    RM20101.DOCNUMBR,

    RM00101.CUSTNAME,

    RM00101.CUSTCLAS,

    RM20101.CHEKNMBR,

    RM00101.SLPRSNID,

    RM20101.CSPORNBR,

    RM00301.SLPRSNFN,

    RM00301.SPRSNSLN

    FROM        

    RM00101 with (nolock)

    INNER JOIN RM20101 with (nolock) ON RM00101.CUSTNMBR = RM20101.CUSTNMBR

    LEFT OUTER JOIN RM00301 with (nolock) ON RM00101.SLPRSNID = RM00301.SLPRSNID

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Dan Liebl Profile Picture

Dan Liebl 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans