CODEWITHSUNDEEP
sql
Simon
Simon

Reputation: 197

Self join gives wrong output

Consider the following table :

id  |  Name  | City
1   |  Roger | Atlanta
2   |  Roger | Atlanta
3   |  Roger | Miami
4   |  Roger | Miami

Now what I want is if I enter as an input the id 1, the output should be id 2 (the other one that is in the same City). I've tried making a self join on the 'City' column, but it's not working.

Upvotes: 1

Views: 84

Answers (2)

Paul
Paul

Reputation: 141829

It willbe faster to avoid a join anyways and use something like:

SELECT `id` FROM `table` WHERE `City` IN 
  (SELECT `City` FROM `table` WHERE `id` = 1)

You might also want to check that name matches though (in which case it would probably be better to use a join). I'm not sure since you didn't say if that was a requirement, I just assumed it's not.

Upvotes: 0

Sam DeHaan
Sam DeHaan

Reputation: 10325

Would this work correctly?

SELECT t2.id FROM MyTable t1
JOIN MyTable t2 ON (t1.City = t2.City AND t1.id <> t2.id)
WHERE t1.id = @input

Upvotes: 4

Related Questions