Results 1 to 2 of 2

Thread: Adding missing rows in table in 1 select (+ join) command

  1. #1
    Join Date
    Jul 2003
    Posts
    1

    Adding missing rows in table in 1 select (+ join) command

    Hi all! Here's what I need to do :

    I have two tables :
    A B
    a b c d
    ---- ----
    1 z 1 k
    2 x 5 l
    3 c 6 j

    I need a SELECT with JOIN that would give me :
    A
    a b
    ----
    1 z
    2 x
    3 c
    5 NULL
    6 NULL

    so I need to add the missing rows from the A.a and B.c JOIN,
    how can I do that ?

    I can't use a UNION because I can't use MySQL version 4.

    Thanks,

    Simon

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    The only way I can think of doing this without a UNION is writing a procedure and poplulating a temporary table then selecting from that.

    If it was SQL Server then I could do this

    select case
    when a.a is null then b.c
    when a.a is not null then a.a
    end, a.b
    from B b full outer join A a
    on a.a = b.c
    order by 1

    I don't know whether you have equivalent of this in mysql.

Posting Permissions

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