Search code examples
mysqlselectpdoinner-join

"... Where field ON (SELECT ..)" does not work with "select" in a working where clause

I need to run a query that looks at the contents on 3 linked tables:

Table a = container of the data we have to extract

Table b = parent table, which contains the ids with which tables a and c are related

Table c = table containing data on the condition to be satisfied

Then: b-> b.id-> on a.bid and b.bid

I tried to make the selection like this:

SELECT field_a
FROM a
WHERE field_b IN (SELECT b.id FROM b INNER JOIN c ON b.id = 1 WHERE c.aid = 1)

The query:

(SELECT b.id FROM b INNER JOIN c ON b.id = 1 WHERE c.aid = 1)

returns this result:

Array ([0] => Array ([id] => 1))

Because I can not execute

"SELECT field_a FROM a WHERE field_b IN (SELECT ...)"

If I try to do

"SELECT field_a FROM a WHERE field_b IN (1)"

the query works, but I need to extract it first with select

Solution

  • This should be equivalent to your query:

    SELECT field_a
FROM a
WHERE EXISTS(SELECT 1 FROM b
             JOIN c ON b.id = 1
             WHERE a.field_b = b.id
               AND c.aid = 1)

    BUT this query:

    SELECT 1 FROM b
JOIN c ON b.id = 1
WHERE a.field_b = b.id
 AND c.aid = 1

    or

    SELECT b.id FROM b INNER JOIN c ON b.id = 1 WHERE c.aid = 1

    as you have written, is wrong. While it runs and gives correct result, the ON clause is not correct - it has nothing to do with JOIN. You might want to use another EXISTS here, but I am very unsure what are you trying to achieve there...