CODEWITHSUNDEEP
mysqlsql
CapsLk
CapsLk

Reputation: 57

How to fill (join with other table) null values if any from another table?

I have two similar tables, they have the same columns, but different data, both matching different criterions. I want to join Table A to Table B, where some of the values are null.

I tried to look up similar questions but they are not describing my case as far as i can tell.

As an example: Table A looks like

| id | name | age | gender |
  1    Jhon   2      Male
  2    Will   null   null

Table B looks like

| id | name | age | gender |
  1    Jhon   null   null
  2    Will   3      Male

What i would like to make is like

| id | name | age | gender |
  1    Jhon   2      Male
  2    Will   3      Male

I was trying to left join it, but the result is not as expected. My thought maybe i need to inner join it, then left join maybe, but it is a bit blurry yet.

I'm kinda new to joins, so every thought is appreciated.

Thanks in advance.

Upvotes: 3

Views: 4833

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

Did you try this?

select id, name,
       coalesce(a.age, b.age) as age,
       coalesce(a.gender, b.gender) as gender
from a join
     b
     using (id, name);

The issue is less the type of join then in how you combine the values from the two tables in the select.

Upvotes: 0

D-Shih
D-Shih

Reputation: 46249

You can try to use UNION ALL in the subquery to do MAX

SELECT id ,name,MAX(age) age ,MAX(gender) gender
FROM (
    SELECT id ,name , age , gender 
    FROM A
    UNION ALL
    SELECT id ,name , age , gender 
    TABLE B
) t1
GROUP BY id ,name

If your A and B tables schema are the same I would suggest you use only one table and use a flag to split those two part.

Upvotes: 1

Related Questions