CODEWITHSUNDEEP
postgresql
Kilisi
Kilisi

Reputation: 444

Postgresql: two columns the same but third column different, display rows

I have 4 columns id, firstname, last name, reference. Most times the reference is the same but I need to display each row that has the same names, but a different reference in postgresql and order them by the id number. ONLY for those rows which someone has more than one reference. I've tried a bunch of things but just made a mess. Please assist. For example:- 

20 / John / Smith / 675  
21 / John / Smith / 675  
22 / John / Smith / 676  
22 / Joe  / Bloggs/ 651  
24 / Joe  / Bloggs/ 651  
25 / John / Smith / 674

Should return all the John Smith rows, because one or more of his references is dissimilar

20 / John / Smith / 675  
21 / John / Smith / 675  
22 / John / Smith / 676  
25 / John / Smith / 674

I can use this to get a count, but I want to display the full rows

    select firstname, lastname, count (distinct id)
    from transfer
    group by firstname, lastname
    having count(distinct id) >= 2

Upvotes: 0

Views: 1426

Answers (2)

wildplasser
wildplasser

Reputation: 44240

You don't need to count, you only have to check if (at least) one record with a different value for reference for the same person exists:

SELECT *
FROM transfer t
WHERE EXISTS (
  SELECT * FROM transfer x
  WHERE x.firstname = t.firstname     -- same name
    AND x.lastname = t.lastname
    AND x. reference <> t.reference   -- different reference
  );

Upvotes: 1

Renzo
Renzo

Reputation: 27414

This is a possible answer:

select id, firstname, lastname, reference
from transfer t1
where 1 < (select count(distinct reference) 
           from transfer t2
           where t1.firstname = t2.firstname and t1.lastname = t2.lastname)

Upvotes: 1

Related Questions