CODEWITHSUNDEEP
sqlsql-serverjoin
nick_v1
nick_v1

Reputation: 1664

SQL join query help - data validation

I have a table with two columns, city and area in a SQL Server 2008 DB. There are 9 distinct area values that apply to each city. For example, 111, 222, 333, 444, 555, 666, 777, 888, 999. So the table would look like

 city,area
 city1,111
 city1,222
 .... 
 city1,999
 city2,111
 city2,222
 .....
 city2,999
 etc...

I would ideally like a result set with the city and the missing area for each one. Is there a way to write a select statement that would do this?

I realized that not each city has all 9 values, there are 61 cities that are missing at least one value, however I can't seem to identify which value is missing.

Upvotes: 0

Views: 1755

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

please tell if for any sample data it don't work.

declare @t table(city varchar(50),area int)
insert into @t values('city1',111),('city1',222),('city1',444),('city2',111),('city2',222),('city2',333)
,('city2',444),('city2',555),('city2',777)
;with CTE as
(select *,ROW_NUMBER()over(partition by city order by area)rn from @t
)
select * from cte a 
inner join cte b on a.city=b.city and b.rn-a.rn=1
where b.area-a.area>111

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Yes, you can do this. Create all combinations of city and area, then use a left outer join to find the missing values.

select c.city, a.area
from (select distinct city from cityarea) c cross join
     (select distinct area from cityarea) a left outer join
     cityarea ca
     on ca.city = c.city and
        ca.area = a.area
where ca.city is null;

Upvotes: 2

Related Questions