CODEWITHSUNDEEP
mysqlsql
misha07380
misha07380

Reputation: 81

How to return records by another column id from same table?

T1 Heroes

ID    NAME FRIEND
001   TOM  NULL
002   JEK  NULL
003   MAX  001
004   JIZ  NULL
005   ZAK  002

How to return all friends?

Result should be like:

001 TOM NULL
002 JEK NULL

I tried this:

SELECT *
FROM Heroes
WHERE ID in ( SELECT * FROM Heroes WHERE ID IS NOT NULL)

Upvotes: 1

Views: 540

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You are on the right track, you just need to pick the right column in the subquery:

SELECT h.*
FROM Heroes h
WHERE h.ID IN (SELECT h2.friend FROM Heroes h2);

I prefer EXISTS for this purpose:

SELECT h.*
FROM Heroes h
WHERE EXISTS (SELECT h2.friend FROM Heroes h2 WHERE h2.friend = h.ID);

This often has better performance. But IN is fine.

Upvotes: 2

Related Questions