CODEWITHSUNDEEP
mysqlsql
Surc
Surc

Reputation: 45

sql query join with no doubles

i am struggling with a query,
Table1 

startdate  | enddate   | name    | examination 
2020-02-01 | 2020-02-01| JohnDoe | xyz

Table2 

begindate  | enddate    | name    | mutation 
2020-02-01 | 2020-02-07 | JohnDoe | Away
2020-03-01 | 2020-03-01 | JohnDoe | Away

Query:

SELECT a.begindate
     , a.enddate
     , IF((a.begindate BETWEEN b.begindate AND b.enddate ), b.mutation, a.exam) value
  FROM table1 a
  JOIN table2 b 
    ON a.name = b.name

Result

a.begindate | a.enddate  | value
2020-02-01  | 2020-02-01 | Away
2020-02-01  | 2020-02-01 | xyz

But i expect only to see all records from table1 and if the begindate from table 1 is between the begindate and enddate then show the value of table2.mutation without double records. tried left/right joins but without result.

How can i fix this?

Upvotes: 2

Views: 62

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you want something like this:

select t1.startdate, t1.enddate, t1.name, t2.mutation
from table1 t1 left join
     table2 t2
     on t1.name = t2.name and
        t1.startdate <= t2.enddate and
        t1.startdate >= t2.startdate

Upvotes: 0

forpas
forpas

Reputation: 164099

Use a LEFT JOIN and set the condition a.begindate between b.begindate and b.enddate in the ON clause:

select 
  a.begindate, a.enddate, 
  case when b.name is not null then b.mutation else a.exam end as value
from table1 as a
left join table2 as b on a.name = b.name and a.begindate between b.begindate and b.enddate

Instead of the case expression you could also use:

coalesce(b.mutation, a.exam)

Upvotes: 1

wp78de
wp78de

Reputation: 18950

It looks like you want to LEFT JOIN like this:

SELECT a.begindate
       ,a.enddate
       ,b.mutation
       ,a.exam
FROM table1 AS a 
LEFT OUTER JOIN table2 AS b 
    ON a.name = b.name 
    AND a.begindate BETWEEN b.begindate AND b.enddate

If you want to combine examination and mutation into a single field use a CASE statement:

CASE WHEN b.mutation IS NOT NULL THEN b.mutation ELSE a.exam END AS value

Upvotes: 1

Related Questions