I'm writing this query:

SELECT DISTINCT(o.id), o.status,  p.excelID
FROM orders as o
LEFT JOIN (SELECT DISTINCT(orderId) id,  excelID FROM parcels) as p on o.id = p.id
WHERE o.id is not null and p.id is not null

This is example of query records:

Like you can see i have duplicates in id column even when im using DISTINCT function and how can i write question to query to get records like:

CodePudding user response：

Incase you are interested in maximum value of excelID in case multiple excelID is available. Try this:

SELECT DISTINCT(o.id), o.status,  max(p.excelID) as excelID
FROM orders as o
LEFT JOIN (SELECT DISTINCT(orderId) id,  excelID FROM parcels) as p on o.id = p.id
WHERE o.id is not null and p.id is not null
group by o.id, o.status