Results 1 to 7 of 7

Thread: join tables

  1. #1
    Join Date
    Jun 2006
    Posts
    14

    Unanswered: join tables

    Hi all,
    I have a problem with two tables:

    T1:
    number date value
    p1 1.4. 50
    p1 3.4. 20
    p2 2.4. 30
    p2 3.4. 30
    p2 4.4. 60
    :
    T2:
    date
    1.4.
    2.4.
    3.4.
    4.4.

    I need a table where is one row for every number and every day.

    table I need:
    number date value
    p1 1.4. 50
    p1 2.4. 0
    p1 3.4. 20
    p1 4.4. 0
    p2 1.4. 0
    p2 2.4. 30
    p2 3.4. 30
    p2 4.4. 60

    is there an easy way to do this???

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    ... t2 left join t1 ...

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT      comby_combo.number
            , comby_combo.date
            , value        = COALESCE(t1.value, 0)
    FROM    --all date number combos
            (
                SELECT    DISTINCT 
                          t1.number
                        , t2.date
                FROM    t1
                CROSS JOIN
                        t2
            ) AS comby_combo
    LEFT OUTER JOIN 
            t1
    ON    t1.number        = comby_combo.number
        AND t1.date        = comby_combo.date

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    missed number

    select t.number, t2.date, value=coalesce(t1.value, 0)
    from t2
    cross join (select number from t1 group by number)t
    left join t1
    on t1.date=t2.date
    and t.number=t1.number

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why is the cross join needed?
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Why is the cross join needed?
    Have a look at the data. Then look at the result set. Where does this row exist in the data?
    >>p1 2.4.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Touché, I missed that detail.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •