Question 183773 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – JOIN with WHERE IN subquery not recognizing WHERE (not narrowing results)

I am trying to select with a join of a table, then use where to exclude some results, like this:

SELECT * FROM table1
LEFT JOIN table2 ON table1.var = table2.var
WHERE 
table1.var IN (select var from table3 WHERE var NOT IN (SELECT var FROM table4))

This is not working. The JOIN is working, but the results are not being reduced for the WHERE subquery. I tried altering the WHERE to WHERE var equals a non-matching value. That successfully filtered the results to 0. Thus, the JOIN with the WHERE works, it just is not working with the subquery.

Help!

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I could not get either of the provided solutions to work. I solved the problem, however.

    The issue seemed to be whenever I would select * from any joined table. Thus, this version derived from the @SelVazi solution did not work/filter:

    SELECT * FROM table1
LEFT JOIN table2 ON table1.var = table2.var
WHERE 
table1.var IN (select var from table3 WHERE var NOT IN (SELECT var FROM table4))

    Nor did it work (filter) to add * from either of the joined tables, for example:

    SELECT table1.*, table2.*, table3.somevar FROM table1
LEFT JOIN table2 ON table1.var = table2.var
WHERE 
table1.var IN (select var from table3 WHERE var NOT IN (SELECT var FROM table4))

    SOLUTION. What DID work (filter) was to select * from the first table, then select specific columns of other names from joined tables, like this for example:

    SELECT table1.*, table2.somevar, table3.somevar2, table3.somevar3 FROM table1
LEFT JOIN table2 ON table1.var = table2.var
WHERE 
table1.var IN (select var from table3 WHERE var NOT IN (SELECT var FROM table4))

    Adding the selection of all columns from either or both of the joined tables, either the INNER JOINed or LEFT JOINed tables, seemed to override the filtering of the JOINs. In playing with this more, I realized that it is the selection of the joining column (var in this case) in the select for a joined table that specifically caused the issue. Thus, this did not work (filter):

    SELECT table1.*, table2.var, table3.somevar2, table3.somevar3 FROM table1
LEFT JOIN table2 ON table1.var = table2.var
WHERE 
table1.var IN (select var from table3 WHERE var NOT IN (SELECT var FROM table4))

    This is not how I have understood the JOINs to work. Perhaps someone can comment to explain. I should also note that var has the same name in table1, table2, table3, and table4 in the database. I wonder if that causes the issue.


  2. 0

    It appears that the issue lies with the usage of the subquery in the WHERE clause. To exclude certain results based on a subquery, you can utilize a LEFT JOIN along with NULL values.

    SELECT * FROM table1
LEFT JOIN table2 ON table1.var = table2.var
WHERE table1.var NOT IN (
SELECT var
FROM table3
WHERE var IN (SELECT var FROM table4)
)
    Login or Signup to reply.
  3. 0

    According to your query, I think you want data combined by var from table1, table2, and table3, but not not exists in table4. If that’s what you’re after, try this :

    SELECT t1.*, t2.*, t3.*
FROM table1 t1
INNER JOIN table2 t2 on t1.var = t2.var
INNER JOIN table3 t3 on t1.var = t3.var
LEFT JOIN table4 t4 on t3.var = t4.var
WHERE t4.var is null
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search