Need help with SQL joins

  • Ysa

    Mr or Mrs. 500

    Points: 522

    I need help with SQL joins when I joins the tables everything is fine until I join the check number,

     

    I need posted and unposted Invoices that I can filter by ORG and Object

     

    I was trying to use the CTE with some system view, but it did not work

     

    So I am attaching the tables, please help.

    Attachments:
    You must be logged in to view attached files.
  • pietlinden

    SSC Guru

    Points: 62416

    Without CREATE TABLE and INSERT scripts (sample data), there's no way to answer your question.

    What does "didn't work" mean?

  • Ysa

    Mr or Mrs. 500

    Points: 522

    Well those tables exist on the DB, didnt work means I dont get the result with my CTE

     

    Thank you anyway

  • Jonathan AC Roberts

    SSCoach

    Points: 16843

    You really should read this before you post a question How to post data/code on a forum to get the best help

    Here is a script that will set up your data:

    SELECT * 
    INTO #vendorcode
    FROM (VALUES
    (001, 144),
    (002, 145),
    (003, 146),
    (004, 147),
    (005, 148),
    (006, 149)
    )T(vendorcode,VendorId);
    GO
    SELECT *
    INTO #gl_ledger
    FROM (VALUES
    ('020', 110101, 'ACCOUNT 1'),
    ('021', 110102, 'ACCOUNT 2'),
    ('022', 110103, 'ACCOUNT 3'),
    ('023', 110104, 'ACCOUNT 4')
    )T(Org, obj, Description)
    GO
    SELECT *
    INTO #Vendor
    FROM (VALUES
    (001, 'amazon'),
    (002, 'Office Depot'),
    (003, 'Team Office'),
    (004, 'Azure'),
    (005, 'Cloud SA'),
    (006, 'testing Inc')
    )T(VendorCode,VendorName)
    GO
    SELECT *
    INTO #GL_DETAILS
    FROM (VALUES
    ('020', 110101, 836, 001),
    ('020', 110101, 836, 002),
    ('020', 110101, 837, 001),
    ('021', 110102, 838, 004),
    ('022', 110103, 838, 005)
    )T(Org, Object, Journal, VendorCode)
    GO
    SELECT *
    INTO #INVOICE
    FROM (VALUES
    (144, 'y', '01/01/2019',4568),
    (145, 'n', NULL, NULL),
    (144, 'n', NULL, NULL),
    (145, 'y', '05/05/2019', 4561),
    (146, 'y', '05/05/2019', 6468),
    (148, 'n', '06/06/2019', 5466)
    )T(VendorId, posted, CheckDate, checkN)
    GO

    Here is a query that will get the results something like you want, it has a few more rows than you expect but I think that might be your data that's at fault.

    SELECT gl.Org, gl.obj, gl.Description, gd.Journal, i.posted, i.CheckDate, i.checkN, v.VendorName
    FROM #gl_ledger gl
    INNER JOIN #GL_DETAILS gd
    ON gd.Org = gl.Org
    AND gd.Object = gd.Object
    INNER JOIN #vendorcode vc
    ON vc.vendorcode = gd.VendorCode
    INNER JOIN #INVOICE i
    ON i.VendorId = vc.VendorId
    INNER JOIN #Vendor v
    ON v.VendorCode = gd.VendorCode
    GO
    DROP TABLE #GL_DETAILS
    GO
    DROP TABLE #gl_ledger
    GO
    DROP TABLE #INVOICE
    GO
    DROP TABLE #Vendor
    GO
    DROP TABLE #vendorcode
    GO
  • Ysa

    Mr or Mrs. 500

    Points: 522

    Thank you but it didnt work !

  • Jonathan AC Roberts

    SSCoach

    Points: 16843

    Ysa wrote:

    Thank you but it didnt work !

    Do you want to be more specific, maybe say what didn't work?

  • Ysa

    Mr or Mrs. 500

    Points: 522

    I got duplicates, so I was trying to create CTE's

  • Jonathan AC Roberts

    SSCoach

    Points: 16843

    Ysa wrote:

    I got duplicates, so I was trying to create CTE's

    There weren't any duplicates in the results of the code I supplied.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply