CODEWITHSUNDEEP
sqlsubquery
sanjay
sanjay

Reputation: 1020

check two constrains in subquery

I have two tables like this

FirstTable

Name Age District

AAA 20 North

BBB 21 South

CCC 19 West

and

SecondTable

Name Age District

ZZZ 19 North

YYY 20 South

XXX 21 North

WWW 24 North

VVV 20 North

UUU 20 West

so my goal is get the first row name of first table (this case 'AAA') and match from second table who live in same district and age is equal or greater than one or less than one (this case age=20,age=19,ane=21 from first table).

to do that I wrote a query like this

SELECT *

FROM Secondtable

WHERE Age=(SELECT Age FROM FristTatle WHERE Name='AAA') OR

  Age+1=(SELECT Age FROM FristTatle WHERE Name='AAA') OR

  Age-1=(SELECT Age FROM FristTatle WHERE Name='AAA')

  AND

  District=(SELECT District FROM FirstTable WHERE Name='AAA')

but this return names who in different District (here it returns YYY and UUU). is there any problem in my query???

Upvotes: 0

Views: 50

Answers (1)

vc 74
vc 74

Reputation: 38179

select * 
from FirstTable
inner join SecondTable
on SecondTable.District = FirstTable.District And
(SecondTable.Age - FirstTable.Age) between -1 and 1
where FirstTable.Name = 'AAA';

should do it

Upvotes: 2

Related Questions