SQL join two tables selecting only one ID-CodePudding

I have the following problem and I have no idea of how to solve it...

We got 2 tables:

COMPANIES ESTIMATED

id	name	estimation
1	AMAZON	True
2	GOOGLE	True
3	FACEBOOK	True

COMPANIES MIXED

id	name	estimation
1	AMAZON	False
2	GOOGLE	False
3	FACEBOOK	False
4	TESLA	False

Well, I want to JOIN BOTH TABLES but DONT REPEAT IDs. If an ID appears 2 times in the result table, I want to KEEP THE TRUE one and discard the False one.

Example response I expected:

COMPANIES FINAL

id	name	estimation
1	AMAZON	True
2	GOOGLE	True
3	FACEBOOK	True
4	TESLA	False

In this example you may think: "I add TESLA to first table, EZ" but is not the answer I am looking for. I need something like an OUTTER JOIN or something like that. Im trying but it's impossible for me.

Thanks guys.

CodePudding user response：

UNION ALL the tables. GROUP BY the result. Use MAX() to get true if available, otherwise false.

select id, name, max(estimation)
from
(
  select id, name, estimation from COMPANIES_ESTIMATED
  union all
  select id, name, estimation from COMPANIES_MIXED
) dt
group by id, name

CodePudding user response：

If for all records in COMPANIES ESTIMATED table estimation = 'true' And for all records in COMPANIES MIXED table estimation = 'false' Next query can help:

SELECT * FROM `COMPANIES_ESTIMATED`
UNION
SELECT * FROM `COMPANIES_MIXED` WHERE id NOT IN (SELECT id FROM `COMPANIES_ESTIMATED`)

Otherwise

SELECT id, name, MAX(estimation) FROM (
    SELECT * FROM `COMPANIES_ESTIMATED`
    UNION
    SELECT * FROM `COMPANIES_MIXED`) AS all_companies
GROUP BY id, name;

CodePudding user response：

just to mix it up a bit you could do it with a full outer join. not sure which rdbms you are using but here is one in sql server

select IIF(a.id>b.id, a.id, b.id) id,
IIF(a.name>b.name, a.name, b.name) Name,
IIF(a.estimation>b.estimation, a.estimation, b.estimation) estimation
from companies_estimated a
full outer join companies_mixed b
on (a.id = b.id and a.name = b.name);

here is the fiddle http://sqlfiddle.com/#!18/b87f66/13

CodePudding user response：

Assuming there are other columns to bring along:

with data as (
    select *, row_number()
        over (partition by id order by estimation desc) rn
    from
    (
        select id, name, estimation, ... from COMPANIES_ESTIMATED
        union all
        select id, name, estimation, ... from COMPANIES_MIXED
    ) t
)
select * from data where rn = 1

CodePudding user response：

You could also use a FULL OUTER JOIN

SELECT 
  id = COALESCE(estimated.id, mixed.id), 
  name = COALESCE(estimated.name, mixed.name), 
  estimation = COALESCE(estimated.estimation, mixed.estimation)
FROM 
  estimated 
  FULL OUTER JOIN mixed
  ON estimated.id = mixed.id
ORDER BY
  id

Example at http://sqlfiddle.com/#!18/dc483a/1