Post reply

Need help with SQL joins

  • June 11, 2019 at 3:49 pm

    #3652379

    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.

  • June 11, 2019 at 5:59 pm

    #3652440

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

    What does "didn't work" mean?

  • June 11, 2019 at 6:16 pm

    #3652443

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

     

    Thank you anyway

  • June 11, 2019 at 7:22 pm

    #3652469

    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

  • June 13, 2019 at 12:24 am

    #3653012

    Thank you but it didnt work !

  • June 13, 2019 at 12:34 am

    #3653015

    Ysa wrote:

    Thank you but it didnt work !

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

  • June 13, 2019 at 2:08 am

    #3653058

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

  • June 13, 2019 at 9:48 am

    #3653128

    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 7 (of 7 total)

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