Shakhnozakhon Muminzoda Shakhnozakhon Muminzoda - 7 months ago 15
SQL Question

sql one segment has many methods but all of them should have one date how to check for the ones that dont have

I have a table looks like this:

method year segment
ABC 2014 AB
CAB 2014 AB
PAU 2013 AB
COR 2015 CD
PRK 2016 IK


All segments should have same year. So I need to identify how many of them has different year. Its a mistake.

Result should be

method year segment
PAU 2013 AB


or


Error = 1


Can you help me with the code?
So far I tried something like this but it gives me whole list:

create table E1 as
select segment, dat_start
from pd_segment a
where a.segment in (select b.segment from pd_segment b
group by b.segment
having count (b.dat_start NE a.dat_start-1))

Answer

let's say our table name is "MyTable"

this query will report segment with more then 1 year:

select distinct segment from MyTable
Group by segment
having count(distinct year)>1

then if you want all the other columns data you can join this result with the table itself

select Mytable.* from MyTable join (
select distinct segment from MyTable
Group by segment
having count(distinct year)>1
) as x on x.segment=Mytable.segment
Comments