How to Intersect multiple tables in mysql

Question

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));

data consists of 6 columns age, gender, shoesize, weight, height, eyes. It is stored in one table in the database, and the temporary tables are just subsets of the original table, each with 1 condition applied to it. — Steven, Apr 08 '16 at 02:24

score 0 · Answer 1 · answered Apr 08 '16 at 02:39

0

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.

answered Apr 08 '16 at 02:39

Ezequiel Tolnay

4,302
1
19
28

This doesn't work :/ I get an error that the query has improper syntax (i.e. fetch_array on non-object) – Steven Apr 08 '16 at 07:46
@Steven the query does not include any fetch_array, how could it fail due to that? – Ezequiel Tolnay Apr 08 '16 at 07:50
I use the query in a mysqli query statement and then fetch_array to get the result, which fails when the query statement is invalid. – Steven Apr 08 '16 at 07:56
I have also confirmed it by repeating the temporary table process directly in sql and trying this query. – Steven Apr 08 '16 at 07:57
@Steven Can you produce the error message you get when running this query exactly as it is here? – Ezequiel Tolnay Apr 08 '16 at 07:59
ah ok so I just read through the version of this in the mysqli->query statement and I say an extra " ) " that did not belong. That is why there was an error. now the mysqli and the direct sql query are the same in that they pull up no results which I assumed before was it not working, but now I realize that the query is correct in syntax but just not correctly identifying specific intersecting results. I inserted 10 results that all met the criteria "age > 10 and shoesize > 6 and height > 60" and but none result after the query. – Steven Apr 08 '16 at 08:15
@Steven Are there any NULL values in any of the tables? – Ezequiel Tolnay Apr 08 '16 at 08:17
nope all values are either float or string depending on the field and are filled in – Steven Apr 08 '16 at 08:19
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/108602/discussion-between-ziggy-crueltyfree-zeitgeister-and-steven). – Ezequiel Tolnay Apr 08 '16 at 08:22

How to Intersect multiple tables in mysql

1 Answers1