Professional Web Applications Themes

Full Outer Join, Merge Result - MySQL

Hello Internet! Since MySQL doesn't have a native FULL OUTER JOIN, I simulated it, using a LEFT OUTER JOIN, and RIGHT OUTER JOIN, UNIONing the result together. The result is like this: id A id B 53 8 53 15 9 7 9 20 55 7 55 16 54 2 NULL NULL 50 2 50 10 47 2 47 11 57 1 NULL NULL 58 1 58 4 NULL NULL 45 4 NULL NULL 41 3 Where some ids have values for both A and B, but some only have one, with NULL in the other. (Also, NULL in the ...

  1. #1

    Default Full Outer Join, Merge Result

    Hello Internet!
    Since MySQL doesn't have a native FULL OUTER JOIN, I simulated it,
    using a LEFT OUTER JOIN, and RIGHT OUTER JOIN, UNIONing the result
    together. The result is like this:

    id A id B
    53 8 53 15
    9 7 9 20
    55 7 55 16
    54 2 NULL NULL
    50 2 50 10
    47 2 47 11
    57 1 NULL NULL
    58 1 58 4
    NULL NULL 45 4
    NULL NULL 41 3

    Where some ids have values for both A and B, but some only have one,
    with NULL in the other. (Also, NULL in the id field)

    I am trying to merge the id fields into one, so I can reference their
    respective values.

    Can anyone help me with this? Thanks in advance,

    -- whit nelson

    Whit Guest

  2. #2

    Default Re: Full Outer Join, Merge Result

    Whit Nelson wrote: 

    We might be able to help if you cold actually explain what you want!

    How's about showing us the output that you would like, that'd be a good
    start!


    Paul Guest

  3. #3

    Default Re: Full Outer Join, Merge Result

    On Feb 17, 4:51 pm, "Paul Lautman" <com>
    wrote: 





    >
    > We might be able to help if you cold actually explain what you want!
    >
    > How's about showing us the output that you would like, that'd be a good
    > start![/ref]

    Sure. So let's say this is my input:

    id A id B
    53 8 53 15
    NULL NULL 9 20
    55 7 55 16
    54 2 NULL NULL

    You'll note how there are 2 id fields. Sometimes they match, and
    sometimes one of them is NULL. I would like to merge these two rows so
    that there is only 1 id field. Such as this:

    id A B
    53 8 15
    9 NULL 20
    55 7 16
    54 2 NULL

    Eventually, I would like to replace the NULL values with zeros, so I
    can do some math on them.

    -- whit

    Whit Guest

  4. #4

    Default Re: Full Outer Join, Merge Result

    On Feb 17, 5:04 pm, "Whit Nelson" <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > Sure. So let's say this is my input:
    >
    > id A id B
    > 53 8 53 15
    > NULL NULL 9 20
    > 55 7 55 16
    > 54 2 NULL NULL
    >
    > You'll note how there are 2 id fields. Sometimes they match, and
    > sometimes one of them is NULL. I would like to merge these two rows so
    > that there is only 1 id field. Such as this:
    >
    > id A B
    > 53 8 15
    > 9 NULL 20
    > 55 7 16
    > 54 2 NULL
    >
    > Eventually, I would like to replace the NULL values with zeros, so I
    > can do some math on them.
    >
    > -- whit[/ref]

    Should I consider the COALESCE function? I'm not that familiar with
    it, but it looks to handle this type of NULL-value situation ...

    -- whit

    Whit Guest

  5. #5

    Default Re: Full Outer Join, Merge Result

    Whit Nelson wrote: 
    >> 
    >> 
    >> 
    >> 
    >> 
    >>
    >> We might be able to help if you cold actually explain what you want!
    >>
    >> How's about showing us the output that you would like, that'd be a
    >> good start![/ref]
    >
    > Sure. So let's say this is my input:
    >
    > id A id B
    > 53 8 53 15
    > NULL NULL 9 20
    > 55 7 55 16
    > 54 2 NULL NULL
    >
    > You'll note how there are 2 id fields. Sometimes they match, and
    > sometimes one of them is NULL. I would like to merge these two rows so
    > that there is only 1 id field. Such as this:
    >
    > id A B
    > 53 8 15
    > 9 NULL 20
    > 55 7 16
    > 54 2 NULL
    >
    > Eventually, I would like to replace the NULL values with zeros, so I
    > can do some math on them.
    >
    > -- whit[/ref]

    You could use 2 COALESCEs but I think IFNULL is better in this case.
    Assuming that you have aliased your tables as a and b, you could try:

    SELECT IFNULL(a.id,b.id), IFNULL(a.A,0), IFNULL(b.B,0) FROM .....


    Paul Guest

  6. #6

    Default Re: Full Outer Join, Merge Result

    On Feb 18, 9:50 am, "Paul Lautman" <com>
    wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]






    >
    > You could use 2 COALESCEs but I think IFNULL is better in this case.
    > Assuming that you have aliased your tables as a and b, you could try:
    >
    > SELECT IFNULL(a.id,b.id), IFNULL(a.A,0), IFNULL(b.B,0) FROM .....[/ref]

    Marvelous! Many thanks.

    -- whit

    Whit Guest

Similar Threads

  1. Outer join?
    By Ignoramus23298 in forum MySQL
    Replies: 5
    Last Post: May 22nd, 09:06 AM
  2. OUTER JOIN
    By jorgepino in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 23rd, 12:59 PM
  3. FULL OUTER JOIN
    By beckydub in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 13th, 02:54 PM
  4. Replies: 2
    Last Post: September 18th, 09:59 PM
  5. outer join, cross join, union? DDL/sample data incl.
    By Kevin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 08:01 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139