CODEWITHSUNDEEP
sqlpostgresql
user2791187
user2791187

Reputation: 5

Finding duplicates in a column B using column A?

I have a table that looks like this (sorry for the terrible looking table)

bnavn   | emnekode | grnr      
------------------------------
moetd   | inf1300  | gruppe10
moetd   | inf2220  | gruppe10       
amirf   | inf1300  | gruppe7    
amirf   | inf2220  | gruppe7    
hannams | inf1300  | gruppe1    
ninacma | inf2220  | gruppe3

I need to

A: Return the bnavn where emnekode is inf1300 & inf 2220 (which was easy)

B: Return the bnavn where grnr for inf1300 & inf2220 are duplicates.

I'm having trouble doing part B - I've tried JOIN and WHERE EXISTS, but nothing seems to be working correctly.

My code so far is:

 SELECT DISTINCT bnavn
 FROM emnestud
 WHERE emnekode IN ('inf1300', 'inf2220')

I'm having a lot of trouble setting up the next part, and would appreciate any help.

Upvotes: 0

Views: 68

Answers (3)

heretolearn
heretolearn

Reputation: 6545

Try this:

SELECT DISTINCT bnavn 
FROM emnestud 
WHERE grnr in(
  SELECT grnr
   FROM emnestud
    WHERE emnekode IN ('inf1300', 'inf2220')
    GROUP BY grnr
    HAVING COUNT(1)>1);

SQLFIDDLE

Upvotes: 0

Federico
Federico

Reputation: 479

This code return the rows that are repeated for emnekode

SELECT emnekode
 FROM emnestud
GROUP BY emnekode
HAVING
 COUNT(*) > 1

this is helpful for you?

Upvotes: 0

Borik
Borik

Reputation: 438

SELECT bnavn
 FROM emnestud
 WHERE emnekode IN ('inf1300', 'inf2220')
group by bnavn
having count(1)>1

Upvotes: 1

Related Questions