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

User · Answer

This may work for you   select t1   from table t1 join  select t2 Chromosome  t2 Locus     from table2     group by t2 Chromosome  t2 Locus     having count     gt  1  u on u Chromosome   t1 Chromosome and u Locus   t1 Locus

User · Answer

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    gt 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

User · Answer

One way of doing this is via an exists clause   select   from genes g where exists  select null from genes g1  where g locus   g1 locus and g chromosome   g1 chromosome and g id  lt  gt  g1 id    Alternatively  in MySQL you can get a summary of all matching ids with a single table access  using group concat   select group concat id  matching ids  chromosome  locus  from genes group by chromosome  locus having count     gt  1

User · Answer

The problem is GROUP BY - if you group results by Locus  you only get one result per locus   Try   SELECT   FROM Genes WHERE Locus    3  AND Chromosome    10     If you prefer using HAVING syntax  then GROUP BY id or something that is not repeating in the result set

User · Answer

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

[sql] Select multiple rows with the same value(s)

Examples related to sql

Examples related to rows