CODEWITHSUNDEEP
mysqlsql
dave
dave

Reputation: 11975

How do I SELECT data based on pairs of columns (themselves the result of a SELECT)?

Suppose I have the following table structure:

TABLE session
    int entity_id
    int user_id
    int status

TABLE entity_user
    int entity_id
    int user_id
    int target

The session table logs interactions by different users on different entities. The entity_user table specifies which users have access to each entity. Significantly, each user can have access to more than one entity.

I want to select (entity, user) pairs from the session table based on some criteria, eg. a particular status. Having retrieved those pairs, I want to look up the corresponding target in the entity_user table for each pair.

Is there a way to do this cleanly in SQL, ideally with a single query?

My solution so far was to select the pairs, do some offline text processing to concatenate them (with a separator) and then use that text. Thus:

SELECT entity_id, user_id FROM session WHERE status = 100;
-- Returns (101, 234), (204, 157), etc.
-- Post-process this result using # as a separator
SELECT entity_id, user_id, target FROM entity_user
  WHERE CONCAT(user_id, '#', entity_id) IN ( '101#234', '204#157', ...)

This works, but I feel like there should be a pure way to do this in SQL. Any suggestions?

Upvotes: 1

Views: 92

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

You can use pairs for IN in MySQL:

SELECT entity_id, user_id, target
FROM entity_user
WHERE (user_id, entity_id) IN (SELECT user_id, entity_id FROM session WHERE status = 100);

For performance, I would recommend an index on session(session, entity_id, user_id). You can also use a join:

SELECT eu.entity_id, eu.user_id, eu.target
FROM entity_user eu JOIN
     session s
     USING (user_id, entity_id)
WHERE s.status = 100;

For this, indexes on session(status, user_id, entity_id) and entity_user(user_id, entity_id) would be useful.

Upvotes: 1

e4c5
e4c5

Reputation: 53744

Can be done with a combination of subquery and join.

SELECT * FROM (
   SELECT entity_id, user_id FROM session WHERE status = 100 ) as s
LEFT JOIN entity_user ON s.entity_id = entity_user.entity_id and s.user_id = entity_user.user_id

Upvotes: 1

Related Questions