How to select data from a table based on multiple expressions?

Question

I have a view by the name of info and it's structure and data sample is the following:

id	name	contacts
1	ali	1234
1	ali	122
2	john	133
2	john	144
2	john	122
3	mike	111
4	khan	444
5	jan	122
5	jan	155

So I am using the above view data in oracle apex report. I want to search data by id for example I search for id=1, it contains two values in contacts column one of the value which is 122 is also included in another records so the result should also contain all the other records which contain 122 in their contacts column. The expected result which I want is:

id	name	contacts
1	ali	1234
1	ali	122
2	john	133
2	john	144
2	john	122
5	jan	122
5	jan	155

user_1863229 · Answer

We can phrase your requirement as wanting to return any record with id = 1 or any record whose contacts overlap with the contacts of id = 1.

SELECT id, name, contacts
FROM yourTable
WHERE id = 1 OR
      id IN (
          SELECT id
          FROM yourTable
          WHERE contacts IN (SELECT contacts FROM yourTable WHERE id = 1)
      )
ORDER BY id;

How to select data from a table based on multiple expressions?

1 Answers

Demo