Geek asks and answers

SQL: how to filter to return only rows from a result set where certain data in a field appears on several rows of an unfiltered result set?

If I have this data:

code1 code2
  1    10       <-- Desired (1 appears more than once)
  1    11       <-- Desired (1 appears more than once)
  2    20
  3    30       <-- Desired (3 appears more than once)
  3    31       <-- Desired (3 appears more than once)
  4    40
  5    50

... And I want to write a single SQL query , the results of which are as follows:

code1 code2
  1    10       <-- This result appears because 1 appears more than once above
  1    11       <-- This result appears because 1 appears more than once above
  3    30       <-- This result appears because 3 appears more than once above
  3    31       <-- This result appears because 3 appears more than once above

(i.e. one SQL query that returns all rows for which any data in the column is code1displayed more than once) ...

What SQL can I write? Is it possible?

Here is what I still have that doesn't work:

// WARNING!
// INVALID SQL

SELECT 
  code1,
  code2 
FROM
  mytable 
GROUP BY code1,
  code2 
HAVING COUNT(code1) > 1    <-- This line is invalid

// WARNING!
// INVALID SQL

Instead of continuing to fight him ... I thought I would ask StackOverflow. Thank!

(Any MySQL-specific commands are ALLOWED if they can help.)

+1
source share
2 answers

:

SELECT code1, code2
FROM myTable
WHERE code1 IN 
    (SELECT code1 FROM myTable GROUP BY code1 HAVING COUNT(code1) > 1)

INNER JOIN :

SELECT t.code1, code2
FROM myTable t
  INNER JOIN
    (SELECT code1 FROM myTable GROUP BY code1 HAVING COUNT(code1) > 1)
     s on s.code1 = t.code1

SQLFiddle Demo

+2

. :

SELECT DISTINCT code1, from mytable ... HAVING COUNT(code1) > 1

, , code1. code2, , - , DISTINCT .

http://dev.mysql.com/doc/refman/5.5/en/select.html

0

More articles:


All Articles