KBom KBom - 4 months ago 13
SQL Question

How to get column that has more than one record

in SQL I have this currently:

Checklist Name | Version

Checklist A 1.1
Checklist A 2.1
Checklist B 1.1
Checklist C 1.1
Checklist C 2.1
Checklist C 3.1


I am trying to get only the records that have more than one version (so NOT checklist B). This is my desired outcome:

Checklist Name | Version

Checklist A 1.1
Checklist A 2.1
Checklist C 1.1
Checklist C 2.1
Checklist C 3.1


I am having trouble with this because I tried using a having clause and it is only letting me select the checklist name, so this would work

select checklistname
from checklisttable
group by checklistname
having count(version) > 1


The above would product this output:

Checklist A
Checklist C


But this (which is what I need cause I want to see the versions, DOESN'T work):

select checklistname, version
from checklisttable
group by checklistname, version
having count(version) > 1


This returns 0 records.

Thank you so much for any help!

Answer

You have to use your query as a derived table and join it back to the original table to get the rest of the fields:

select t1.*
from checklisttable t1
join (
  select checklistname
  from checklisttable
  group by checklistname
  having count(version) > 1
) t2 on t1.checklistname = t2.checklistname