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 Nelson wrote:
> 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

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!

On Feb 17, 4:51 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> Whit Nelson wrote:
> > 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
>
> 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!

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

On Feb 17, 5:04 pm, "Whit Nelson" <pengypeng...@gmail.com> wrote:
> On Feb 17, 4:51 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>
>
>
> > Whit Nelson wrote:
> > > 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
>
> > 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!
>
> 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

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 Nelson wrote:
> On Feb 17, 4:51 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>> Whit Nelson wrote:
>>> 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
>>
>> 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!
>
> 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

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 .....

On Feb 18, 9:50 am, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> Whit Nelson wrote:
> > On Feb 17, 4:51 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> > wrote:
> >> Whit Nelson wrote:
> >>> 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
>
> >> 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!
>
> > 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
>
> 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 .....

Marvelous! Many thanks.

-- whit