Mixing ANSI 1992 JOINs and COMMAs in a query

by

According to this link, you shouldn’t mix up both notations when building up joins. The comma you are using to join memebers as m, telephone as t, and the subsequent calls to inner join, are triggering the unknown column error.

To deal with it, use CROSS/INNER/LEFT JOIN instead of commas.

Previously, the comma operator (,) and JOIN both had the same
precedence, so the join expression t1, t2 JOIN t3 was interpreted as
((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression
is interpreted as (t1, (t2 JOIN t3)). This change affects statements
that use an ON clause, because that clause can refer only to columns
in the operands of the join, and the change in precedence changes
interpretation of what those operands are.

For pedagogic purpose, I’m adding the query as it, I think, should be:

SELECT m.*, t.*
FROM memebers as m 
    JOIN telephone as t
    JOIN memeberFunctions as mf ON m.id = mf.memeber AND mf.function = 32
    JOIN mitgliedTelephone as mt ON m.id = mt.memeber

Since you’re not joining t and m, the final result will be a cartesian product; you might want it to be revised.

I Hope it helped.

More Related Contents:

  1. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  2. Recreating a query using INNER JOIN? [duplicate]
  3. INNER JOIN ON vs WHERE clause
  4. 1052: Column ‘id’ in field list is ambiguous
  5. How to join two tables mysql?
  6. Join tables with SUM issue in MYSQL
  7. Join between tables in two different databases?
  8. Required to join 2 tables with their FKs in a 3rd table
  9. How can I join multiple SQL tables using the IDs?
  10. Why does MySQL report a syntax error on FULL OUTER JOIN?
  11. How to use mysql JOIN without ON condition?
  12. Cumulative sum over a set of rows in mysql
  13. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  14. MySQL JOIN the most recent row only?
  15. How to find non-existing data from another Table by JOIN?
  16. MySQL Inner Join Query Multiple Tables
  17. Difference between these two joining table approaches?
  18. Laravel join with 3 Tables
  19. MySQL: Inner join vs Where [duplicate]
  20. MySQL INNER JOIN select only one row from second table
  21. MySQL LEFT JOIN 3 tables
  22. how to use a like with a join in sql?
  23. When to use a left outer join?
  24. In what order are MySQL JOINs evaluated?
  25. How to store a one to many relation in MySQL database?
  26. MySQL Multiple Joins in one query?
  27. Simulate lag function in MySQL
  28. Get record counts for all tables in MySQL database
  29. MySQL dynamic-pivot
  30. MySQL LIKE query with underscore

Leave a Comment