CODEWITHSUNDEEP
sqlsql-server-2008
Azhar
Azhar

Reputation: 20678

How to write this sql query

I have these Attributes in a table ABC

Location, Zone, Sector, UnitNo

and want to write a query for them where Location and UnitNo are same but Sector is different means I have sectors in different locations where UnitNo is same

I wrote this one but does not work.

select * from ABC A1 Inner JOIN ABC A2 on A1.Id = A2.ID
WHERE 
A1.Sector <> A2.Sector AND A1.Location = A2.Location ANd A1.UnitNo = A2.UnitNo

please help.

Upvotes: 1

Views: 78

Answers (4)

Madhav
Madhav

Reputation: 2415

Please fine the below code

select * from ABC A1 Inner JOIN ABC A2 
on A1.Id = A2.ID
and A1.Sector <> A2.Sector 
AND A1.Location = A2.Location 
ANd A1.UnitNo = A2.UnitNo

Upvotes: 0

user1881367
user1881367

Reputation: 1

SELECT DISTINCT Sector from ABC where location = unitno;

Upvotes: 0

TechDo
TechDo

Reputation: 18659

I guess you needed the following query

SELECT 
    * 
FROM 
    YourTable
WHERE 
    Location=UnitNo AND 
    Location<>Sector

Upvotes: 0

Orangecrush
Orangecrush

Reputation: 1990

Try this,

select location, unitno
from abc
group by location, unitno
having count(distinct sector) > 1;

Upvotes: 2

Related Questions