Question 21946 in Mysql Question posted in
The official MySQL documentation can be found here.

Join tables to find distinct rows if any duplicate columns on join are fetched on the basis of date – Mysql

I have two tables I need to join these table and there is a possibility that joined table might return duplicate rows but there is column updated date which will be unique so I need to fetch record from these tables and get distinct records from second table

Table-1

Id AccountKey
1 12
2 13

Table-2

Rolekey Account Key **Date **
1 12 2-12-2022
2 12 1-12-2022
3 13 1-12-2022

In the above table I except the result as below
Expections:-

Id AccountKey Date
1 12 2-12-2022
2 13 1-12-2022

But I am getting all the rows means 3, below is what I tried

select table1.id,table1.accountkey,table2.date 
from table1 table1 
JOIN table2 table2 
ON table1.accountkey=table2.accountkey
Tags:

2

Answers


  1. 0

    Let’s say we’re grouping by id and accountKey
    Query below will get as your desired result.

    SELECT a.id, a.accountKey, MAX(b.cdate)
FROM table1 a
JOIN table2 b ON a.accountKey = b.accountKey
GROUP BY a.id, a.accountKey
    Login or Signup to reply.
  2. 0

    Try this

    drop table if exists one;
drop table if exists two;

create table one
(
  ID         [Int]
, AccountKey [Int]
)
;

insert into one
values (1, 12)
     , (2, 13)
;

create table two
(
  Rolekey    [Int]
, AccountKey [Int]
, Date       [Date]
)
;

insert into two
values (1, 12, '2022-12-02')
     , (2, 12, '2022-12-01')
     , (3, 13, '2022-12-01')
;

select one.id
     , one.AccountKey
     , max(Date) as Date
from one, two
where one.AccountKey = two.AccountKey
group by one.id
       , one.AccountKey
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search