0

I'm trying to select a bunch of rows from my first table depending of values from my second table.

To realize that, I came up with the following query :

SELECT *

FROM table2 t2
WHERE t2.id IN(

    SELECT subquery.id

    FROM
    (

        SELECT id

        FROM table1 t1
        WHERE (t1.property=10 AND t1.value=0)

    ) AS subquery

    INNER JOIN
    (

        SELECT id

        FROM table1 t1
        WHERE (t1.property=20 AND i.value=1) 

    ) AS subquery2

    on subquery.id=subquery2.id

)

Depending of the property and value from table1, a specific list of id will be selected to be used as a condition for the final SELECT with the table2.

The subquery in itself is working, I tested it and I could retrieve the good id.

+------+
|  id  |
|------|
|  18  |
|  55  |
¦      ¦

Problem is, the IN isn't working, at the end of the final SELECT I retrieved all the rows from table2 instead of the ones respecting the condition of having the same id as the subquery retrieved earlier.

+------+------+---
|  id  | name |
|------|------|---
|  1   |  xx  |
|  2   |  yy  |
¦      ¦      ¦

So my question is :

  • Why is the subquery ignored in my IN statement and is there a way to bypass that problem ?

EDIT

Here is a SQLFiddle as requested, but strangely, the code I posted is working here but not for my database.

Karaelfte
  • 143
  • 1
  • 12

2 Answers2

0
SELECT * FROM table2 t2
INNER JOIN 
    (SELECT 
            subquery.id
        FROM
            (SELECT 
                id
            FROM
                table1 t1
            WHERE
                (t1.property = 10 AND t1.value = 0)) AS subquery
                INNER JOIN
            (SELECT 
                id
            FROM
                table1 t1
            WHERE
                (t1.property = 20 AND i.value = 1)) AS subquery2 ON subquery.id = subquery2.id)Z ON Z.id=t2.id

You can try above code. Hope this will help.

Sagar Gangwal
  • 7,544
  • 3
  • 24
  • 38
  • Tried it, not working, it keeps getting all the rows from table2 – Karaelfte Jun 08 '17 at 09:19
  • Can you provide O/P for `SELECT subquery.id FROM (SELECT id FROM table1 t1 WHERE (t1.property = 10 AND t1.value = 0)) AS subquery INNER JOIN (SELECT id FROM table1 t1 WHERE (t1.property = 20 AND i.value = 1)) AS subquery2 ON subquery.id = subquery2.id` – Sagar Gangwal Jun 08 '17 at 09:20
0

try like the following; if you are having any null values in the columnn you won't get the expected result.

common_id not in
(
  select common_id from Table1
  where common_id is not null
)

SQL "select where not in subquery" returns no results

Elias
  • 664
  • 2
  • 11
  • 23