Thread: Full Join Issue

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    5
    Rep Power
    0

    Full Join Issue


    Hi,

    I came up with a situation where for instance i have a table with columns ( ID, Date, A, B, C, D, E, F)

    ID, DATE, A, B, C comes from one query
    and
    ID, DATE, D, E, F comes from another query
    so i was using full outer join for both queries to populate the table... I want all the records with matching ID,DATE as well non matching to be part of result set. like for example

    Query 1 outputs:
    ID, DATE, A, B, C
    -------------------------
    ID-1, 1-1-2012, 5, 4, 3
    ID-2, 1-1-2012, 3, 5, 4
    ID-3, 1-1-2012, 9, 5, 4

    Query 2 outputs:
    ID, DATE, D, E, F
    -------------------------
    ID-1, 1-1-2012, 7, 8, 9
    ID-4, 1-1-2012, 9, 5, 4
    ID-3, 1-1-2012, 6, 5, 4

    Desired output is :

    ID, Date, A, B, C, D, E, F
    -------------------------------
    ID-1, 1-1-2012, 5, 4, 3, 7, 8, 9
    ID-2, 1-1-2012, 3, 5, 4, null, null, null
    ID-3, 1-1-2012, 9, 5, 4, 6, 5, 4
    ID-4, 1-1-2012, null, null, null, 9, 5, 4
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    5
    Rep Power
    0

    Full Join Not Working


    Hi,

    I came up with a situation where for instance i have a table with columns ( ID, Date, A, B, C, D, E, F)

    ID, DATE, A, B, C comes from one query
    and
    ID, DATE, D, E, F comes from another query
    so i was using full outer join for both queries to populate the table... I want all the records with matching ID,DATE as well non matching to be part of result set. like for example

    Query 1 outputs:
    ID, DATE, A, B, C
    -------------------------
    ID-1, 1-1-2012, 5, 4, 3
    ID-2, 1-1-2012, 3, 5, 4
    ID-3, 1-1-2012, 9, 5, 4

    Query 2 outputs:
    ID, DATE, D, E, F
    -------------------------
    ID-1, 1-1-2012, 7, 8, 9
    ID-4, 1-1-2012, 9, 5, 4
    ID-3, 1-1-2012, 6, 5, 4

    Desired output is :

    ID, Date, A, B, C, D, E, F
    -------------------------------
    ID-1, 1-1-2012, 5, 4, 3, 7, 8, 9
    ID-2, 1-1-2012, 3, 5, 4, null, null, null
    ID-3, 1-1-2012, 9, 5, 4, 6, 5, 4
    ID-4, 1-1-2012, null, null, null, 9, 5, 4
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    5
    Rep Power
    0
    The query i tried is like:

    Select ID,
    Date,
    Count(Col1) as A,
    Count(Col2) as B,
    Count(Col3) as C,
    T2.D,
    T2.E,
    T2.F
    FROM T1

    FULL OUTER JOIN
    (
    Select ID,
    Date,
    Count(Col4) as D,
    Count(Col5) as E,
    Count(Col6) as F
    FROM T1
    Group By ID, Date, D, E, F
    )T2

    ON T2.ID=ID AND t2.Date=Date
    Group By ID, Date, T2.D, T2.E, T2.F

    ------------------------------------------------------ ------------------------
    The individual SELECT query results are as above in the thread.

    but after full Join it just bring the records from left table and the matching records (like left outer join)
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    which database system is this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    5
    Rep Power
    0
    The query i tried is like:

    Select ID,
    Date,
    Count(Col1) as A,
    Count(Col2) as B,
    Count(Col3) as C,
    T2.D,
    T2.E,
    T2.F
    FROM T1

    FULL OUTER JOIN
    (
    Select ID,
    Date,
    Count(Col4) as D,
    Count(Col5) as E,
    Count(Col6) as F
    FROM T1
    Group By ID, Date, D, E, F
    )T2

    ON T2.ID=ID AND t2.Date=Date
    Group By ID, Date, T2.D, T2.E, T2.F

    ------------------------------------------------------ ------------------------
    The individual SELECT query results are as above in the thread.

    but after full Join it just bring the records from left table and the matching records (like left outer join)
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    5
    Rep Power
    0
    I am using TeraData

IMN logo majestic logo threadwatch logo seochat tools logo