CODEWITHSUNDEEP
phpmysqlintersect
Steven
Steven

Reputation: 119

How to Intersect multiple tables in mysql

I have set up a php page so there are multiple options for data input that gets put into multiple temporary tables, each one separated with querying the database based on 1 condition given in the data input. So if the input is age > 10 and shoesize > 6 and height > 60, there will be three temporary tables table0, table1, and table2 where table0 is only data age > 10 and table1 is only data shoesize > 6 and table 2 is only data height > 60.

I am wondering how to intersect these so I will only get the results that have all requirements met with age > 10 and shoesize > 6 and height > 60. My attempt using the "WHERE EXISTS" clause is below but it doesn't work.

SELECT *
FROM table0 t0
WHERE EXISTS
    (SELECT *
     FROM table1 t1
     WHERE EXISTS
         (SELECT *
          FROM table2 t2
          WHERE t0.age = t1.age = t2.age
            AND t0.shoesize = t1.shoesize = t2.shoesize
            AND t0.height = t1.height = t2.height));

Upvotes: 0

Views: 1924

Answers (1)

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4582

Note that queries like this without relying on a table's primary key become cumbersome, so I'd recommend you add a primary key.

That said, the query you need is quite close to what you had already attempted:

SELECT *
FROM table0 t0
WHERE EXISTS (
          SELECT 1
          FROM table1 t1
          WHERE t1.age = t0.age AND t1.gender = t0.gender
            AND t1.shoesize = t0.shoesize AND t1.weight = t0.weight
            AND t1.height = t0.height AND t1.eyes = t0.eyes) AND
      EXISTS (
          SELECT 1
          FROM table2 t2
          WHERE t2.age = t0.age AND t2.gender = t0.gender
            AND t2.shoesize = t0.shoesize AND t2.weight = t0.weight
            AND t2.height = t0.height AND t2.eyes = t0.eyes)

Note: The query above will work only if none of the values is NULL.

Upvotes: 0

Related Questions