### Thread: How can I select a common rows in a table

1. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Dec 2012
Posts
7
Rep Power
0

#### Selecting A Distinct Columns

Col1 : col2:
........:.......:.
5 : 9 :
........:.........
6 : 3 :
........:.......:..
11 : 9 :
........:.......:..
5 : 9 :
........:.......:..
11 : 8 :
........:.......:.

I want to SELECT col2 WHERE 5 and 11 are common in col1

Which suppose to return :9,9,9

I will appreciate a statement that will do that .thanks
2. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Dec 2012
Posts
7
Rep Power
0

#### How can I select a common rows in a table

I need a help in this assignment

Col1 : col2:
........:.......:.
5 : 9 :
........:.........
6 : 3 :
........:.......:..
11 : 9 :
........:.......:..
5 : 9 :
........:.......:..
11 : 8 :
........:.......:.

I want to SELECT col2 WHERE 5 and. 11, in col1 are common in col2.
Which suppose to return :9,9,9
I will appreciate a statement that will do that .thanks
3. Originally Posted by okesimojs
... WHERE 5 and 11 are common in col1
not sure i understand what you mean by "common"

does this work for you? --
Code:
```SELECT col2
FROM daTable
INNER
JOIN ( SELECT col1
FROM daTable
WHERE col1 IN ( 5 , 11 )
GROUP
BY col1
HAVING COUNT(DISTINCT col1) > 1 ) AS criteria
ON criteria.col1 = daTable.col1```
4. No Profile Picture
Contributing User
Devshed Intermediate (1500 - 1999 posts)

Join Date
Mar 2008
Posts
1,953
Rep Power
382
Now I'm confused. The original message appears to be 'unedited' and says 'common in col2'
!?!
5. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Dec 2012
Posts
7
Rep Power
0
[QUOTE=cafelatte]Now I'm confused.
I was using my phone to answer . That's why I couldn't noticed the edited reply .
Sir, the script you gave me did not return anything .
6. Originally Posted by okesimojs
Sir, the script you gave me did not return anything .
sorry, remove the DISTINCT

results:

9
9
9
8

still not sure if i understand your requirements, because my query returns the 8 as well, since col1=11 occurs more than once
7. I believe they want, where col1 is 5 or 11, all the values of col2 where there are duplicate col2 values.

Not tested!!!

Code:
```SELECT col2
FROM daTable
WHERE col1 in (5, 11)
GROUP BY col2
HAVING COUNT(col2) > 1```

• cafelatte agrees : Indeed!
8. Almost right! Now with added testing!

Code:
```select group_concat(col2)
from daTable
where col1 in (5,11)
group by col2
having count(col2)>1;

+--------------------+
| group_concat(col2) |
+--------------------+
| 9,9,9              |
+--------------------+```