Post reply

Join Help

  • September 8, 2009 at 10:05 am

    #156187

    Hi,

    Here is the scenario -

    Table QALS has Fld1, Fld2, Fld3 ( Records 10K)

    Table QAVE Has Fld1, Fld4 (Records 5K)

    Table JEST Has Fld3, Fld5, Fld6 ( Records 100K)

    I need to make a left outer join for QALS and QAVE so I can get all of the 10K records, then I need to make a regular join between the result of Left outer join (QALS Left Outer QAVE) and TABLE JEST.

    It is kinda like this -

    Select QALS.Fld1,QALS.Fld2, QALS.Fld3, QAVE.Fld4, JEST.Fld5, JEST.Fld5

    (QALS left Outer Join QAVE) Inner Join JEST

    Here are the relationships - QALS.Fld1 = QAVE. Fld1 and QALS.Fld3 = JEST.Fld3

    Thanks for your help.

  • September 8, 2009 at 10:40 am

    #1050641

    Fenicon,

    looks like you just need a subquery.

    SELECT X.Fld1,X.Fld2, X.Fld3, X.Fld4, JEST.Fld5, JEST.Fld5 FROM JEST

    JOIN (

    SELECT QAVE.Fld1, QALS.Fld2, QALS.Fld3, QAVE.Fld4 FROM QALS

    LEFT JOIN QAVE ON QALS.Fld1 = QAVE.Fld1 ) X ON X.Fld3 = JEST.Fld3

    Can't really test it, but let me know if that works for you

  • September 8, 2009 at 12:19 pm

    #1050707

    I think I might be missing something, but it looks like all the joins are going against the QALS table, so you don't need the inner join. Couldn't you get away with something like this

    select Qals.Fld1, Qals.Fld2, Qals.Fld3, Qave.Fld4,

    Jest.Fld5, Jest.Fld6

    from Qals

    left outer join Qave

    on Qals.Fld1 = Qave.Fld1

    inner join Jest

    on Qals.Fld3 = Jest.Fld3

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • September 8, 2009 at 12:33 pm

    #1050721

    IF FLD1 AND FLD3 is FK IN QALS referring to QAVE and JEST

    then I think a simple query like this should yield you the desire result

    SELECT QALS.Fld1,QALS.Fld2, QALS.Fld3, QAVE.Fld4, JEST.Fld5, JEST.Fld6

    FROM JEST

    INNER JOIN QALS

    ON JEST.Fld3 = QALS.Fld3

    LEFT JOIN QAVE

    ON QALS.Fld1 = QAVE.Fld1

    Best Regards,

    Vijay Mishra

  • September 8, 2009 at 12:35 pm

    #1050722

    you are right. I also feels the same

    Best Regards,

    Vijay Mishra

Viewing 5 posts - 1 through 4 (of 4 total)

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