subquery with multiple rows

webnick

Hi, I have one table with a long cardinality. I'd like to get a list of all the folks in the table with more than one login in. Is there an easy way to nest these subquries in MYSQL?!

SELECT w.GROUP, w.SSN, w.PINNO
FROM webusers w
WHERE w.SSN IN (
    SELECT w.SSN, COUNT(w.SSN) AS pins FROM webusers w
    GROUP BY w.SSN
    HAVING pins > 1)
LIMIT 50

Thanks...

Sxooter

I'm not sure I see a need for a subquery here.

webnick

I need to know the w.SSN and w.PINNO for each row with more than one entry, not just the SSNs with more than one...

Sxooter

In that case, your query looks ok to me. Does it run as is?

webnick

No, I get an error with the subquery--

"You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT w.SSN, COUNT(w.SSN) AS pins FROM webusers"

Sxooter

You likely need a different alias in the subquery
select q.ssn from table q

Lars_Berg

SELECT 
    w.GROUP, 
    w.SSN, 
    w.PINNO
  FROM webusers w
 WHERE w.SSN IN (
      SELECT SSN
         FROM webusers
      GROUP BY SSN
      HAVING count(*) > 1)
LIMIT 50