How to select multiple values in a bidirectional many to many relationship based on input array

Question

How to make the select statement below handle multiple inputs (a list) at once and still return a similar resulting list as to that of a single input

It's been hours, and I'm stuck - I need help writing a mysql statement to retrieve a list of ids from a table where a "link" is found.

The idea behind this question is to feed the query an array of id values, and have it return all other id's that are linked, from both sides of the table, to each of the id's passed in from the array into a single comma delineated list.

table structure "team_permissions"

first_teamID  |  second_teamID
  113         |     112
  114         |     112
  118         |     117
  119         |     112
  119         |     113
  119         |     114

Working "get permissions per single team" sql statement -- need to feed a list into this

SELECT first_teamID AS team_permissions 
FROM team_permissions
WHERE second_teamID = ?

UNION ALL

SELECT second_teamID AS team_permissions 
FROM team_permissions
WHERE first_teamID = ?

Expected Single Input -> Output Examples - this works

(? = 112) will yield the result: [113, 114, 119]
(? = 113) will yield the result: [112, 119]
(? = 117) will yield the result: [118]
(? = 119) will yield the result: [112, 113, 114]

Expected Multiple Input -> Output Examples

(? = [112, 113] ) will yield the result: [112, 113, 114, 119]
(? = [113, 119] ) will yield the result: [112, 113, 114, 119]
(? = [112, 114, 118] ) will yield the result: [112, 113, 114, 117, 119]

team_permissions table structure

You are looking for pivot function that does not exist in mysql. You can do it programmatically, by issuing several queries. Look at some examples in here: https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns and here: https://stackoverflow.com/questions/1241178/mysql-rows-to-columns — Ran Avnon, Jul 07 '17 at 03:59

score 1 · Accepted Answer · answered Jul 07 '17 at 04:00

1

Just use WHERE IN, e.g.:

SELECT first_teamID AS team_permissions 
FROM team_permissions
WHERE second_teamID IN (113, 119)
UNION ALL
SELECT second_teamID AS team_permissions 
FROM team_permissions
WHERE first_teamID IN (113, 119)

answered Jul 07 '17 at 04:00

Tim Biegeleisen

502,043
27
286
360

I knew there was a simple answer to this problem. Thanks Tim! The WHERE IN clause did the trick. – gheckman Jul 07 '17 at 05:01

How to select multiple values in a bidirectional many to many relationship based on input array

1 Answers1