CODEWITHSUNDEEP
phpmysqlleft-joininner-join
Jouni Lahtinen
Jouni Lahtinen

Reputation: 47

MySQL LEFT JOIN is causing "duplicate" rows

I have tables "customers" and "plans" and I want to list all customers regardless they have a plan or not. I'm using query to do it

SELECT customer.name, plan.goal
FROM customer
LEFT JOIN plan ON plan.customerid=customer.customerid
ORDER BY customer.name

I also want to see the goal (plan.goal) with the customer name. This works as long as customer does not have a plan or has a single plan. If customer has two or more plans then I get as many rows of customer name as there are plans.

What I want is customer.name and plan.goal from the latest plan. We can assume the higher value in plan.planid is the latest plan.

I guess I should use sub queries and INNER JOINS some how but I just don't get it right now...

Upvotes: 0

Views: 847

Answers (3)

Evan de la Cruz
Evan de la Cruz

Reputation: 1966

I think you should add a boolean/tinyint column to the plan table that says IsLatest or something like that. Then you could do:

SELECT customer.name, plan.goal
FROM customer
LEFT JOIN plan ON plan.customerid=customer.customerid
where testplan.islatest = 1 or testplan.islatest is null
ORDER BY customer.name

Also, I would stay away from the sub-query answers such as

select a from (select b from c where e=f) where g=h

as they don't often perform very well, besides being confusing to read.

Upvotes: 1

Kritner
Kritner

Reputation: 13765

I would think something like this would work:

SELECT customer.name, plan.goal
FROM customer c
inner join plan p on c.customerId = p.customerId
inner JOIN (
    -- grabs the most recent plan per customer
    select max(planId) as planId, customerId
    from plan
    group by customerId
) maxPlan on p.planId = maxPlan.planId
UNION
-- handles customers w/o a plan
select customer.name, null
from customer c
left join plan p on c.customerId = p.customerId
where p.customerId is null
ORDER BY customer.name

Upvotes: 1

Javaluca
Javaluca

Reputation: 857

SELECT c.name, 
( SELECT p.goal
  FROM plan p
  WHERE p.customerid=c.customerid
  AND NOT EXISTS (  SELECT 'a'
                    FROM plan p2
                    WHERE p2.customerid = p.customerid
                    AND p2.planid > p.planId
                  )
)
FROM customer c

Upvotes: 3

Related Questions