Jordan Jordan - 3 years ago 132
SQL Question

SQL query to finds rows containing a value in a column that also appears in another column?

Given a team table represented as below.


id | name | owner_id | members
------|-------------------------------------
1 | Tigers |99 | 501,502,503
2 | Bears |100 | 100,600,601,602
3 | Swans |101 | 700,701,702
...


A team has a name, an owner (a foreign key to a user's table), and members (a list of id's related to users in a user's table).

The point here is that while owners are implicitly team members, their id does not appear in the members column. In the above example, row #2 is an error (id 100 appears in members column).

Question
How can I query the table for rows which have owner_ids that also appear in members column?

Answer Source

Use FIND_IN_SET.

select * from tbl
where find_in_set(owner_id,members)>0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download