MYSQL QUERY: Can you use IN with 2 different fields?-CodePudding

My database has 2 tables. One table with customers and products and another table that links them:

    Table 1: objects (id,name,id_type) where id_type is the type of object (1 client, 2 product)
    Table 2: object_relations (id,id_child,id_parent) where id_child and id_parent are objects.id.

My initial query shows you specific clients that have bought specific products. The products and clients are shown at the following example:

SELECT o.* 
FROM objects AS o 
    LEFT JOIN objects_relations AS o_r ON(o_r.id_child = o.id) 
WHERE o.type=1 
AND EXISTS (
        SELECT 
        FROM objects AS o9 
            LEFT JOIN objects_relations AS o_r9 ON (o_r9.id_child = o9.id) 
        WHERE o9.id=o.id 
        AND o_r9.id_parent=o_r.id_parent 
        AND (
                (o9.id=21 AND o_r9.id_parent=3) 
                OR (o9.id=21 AND o_r9.id_parent=5) 
                OR (o9.id=25 AND o_r9.id_parent=2) 
                OR (o9.id=25 AND o_r9.id_parent=7) 
                OR ...long list 
            )
        )

I'd love to see another way to implement the long list of specific product and clients, like a IN (). Is there a better way?

CodePudding user response：

You can do tuple comparison with the IN ( ) predicate.

AND (o9.id, o_r9.id_parent) IN (
  (21, 3), (21, 5), (25, 2), (25, 7), ...
)

Another option is new syntax supported in MySQL 8.0: the VALUES statement allows you to derive tables of literal values:

SELECT *
FROM objects AS o9
INNER JOIN objects_relations AS o_r9
INNER JOIN (
 VALUES ROW(21, 3), ROW(21, 5), ROW(25, 2), ROW(25, 7), ...
) AS t ON (o9.id, o_r9.id_parent) = (t.column_0, t.column_1)

See https://dev.mysql.com/doc/refman/8.0/en/values.html

CodePudding user response：

You can use nested lists with IN

AND (o9.id, o_r9.id_parent) in ((21, 3), (21, 5), (25, 2), (25, 7))