Select multiple rows with the same value(s)

Question

I have a table, sort of like this:

ID  |  Chromosome | Locus | Symbol | Dominance |
===============================================
1   |      10     |   2   |   A    |   Full    |
2   |      10     |   2   |   a    |   Rec.    |
3   |      10     |   3   |   B    |   Full    |
4   |      10     |   3   |   b    |   Rec.    |

I'd like to select all rows with the same locus and chromosome. For example, rows 3 and 4. There may be more than 2 at a time and they may not be in order.

I tried this:

SELECT *
FROM Genes
GROUP BY Locus
HAVING Locus='3' AND Chromosome='10'

But it always returns row 3, never row 4, even when repeated. I think I'm missing something obvious and simple, but I'm at a loss.

Can someone help?

Answer 1

You need to understand that when you include GROUP BY in your query you are telling SQL to combine rows. you will get one row per unique Locus value. The Having then filters those groups. Usually you specify an aggergate function in the select list like:

--show how many of each Locus there is SELECT COUNT(*),Locus FROM Genes GROUP BY Locus  --only show the groups that have more than one row in them SELECT COUNT(*),Locus FROM Genes GROUP BY Locus HAVING COUNT(*)>1  --to just display all the rows for your condition, don't use GROUP BY or HAVING SELECT * FROM Genes WHERE Locus = '3' AND Chromosome = '10' 

Answer 2

Assuming that you want all rows for which there is another row with the exact same Chromosome and Locus:

You can achieve this by joining the table to itself, but only returning the columns from one "side" of the join.

The trick is to set the join condition to "the same locus and chromosome":

select left.*
from Genes left
inner join Genes right
on left.Locus = right.Locus and 
  left.Chromosome = right.Chromosome and left.ID != right.ID

You can also easily extend this by adding a filter in a where-clause.