seo Question posted in
Some basic information to get you started with this can be found here.

What is wrong with this SQL Query and how to possibly fix it? – SEO

The Idea of the Query is to get all the entries where the seo_path_info has duplicates and the corresponding product is set to active.

My Query is:

SELECT `seo_path_info`,COUNT(`seo_path_info`)
FROM `shopware`.`seo_url` seo
GROUP BY `seo_path_info`
HAVING COUNT(`seo_path_info`)>1
LEFT JOIN product p ON p.id = seo.foreign_key WHERE p.active = '1';

And the corresponding Error:

Query 1 ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘LEFT JOIN product p ON p.id = seo.foreign_key WHERE p.active = ‘1” at line 5

Tags:

3

Answers


  1. 0

    JOIN comes after FROM but before WHERE, and WHERE comes before GROUP BY:

    SELECT seo_path_info, COUNT(seo_path_info)
FROM shopware.seo_url seo
LEFT JOIN product p
    ON p.id = seo.foreign_key AND p.active = '1'
GROUP BY seo_path_info
HAVING COUNT(seo_path_info) > 1;
    Login or Signup to reply.
  2. 0

    Basic SQL syntax is

    SELECT ... 
FROM ... 
JOIN ... ON ... 
WHERE ... 
GROUP BY ... 
HAVING...

    The solution is:

    SELECT seo_path_info,COUNT(seo_other_column)
FROM shopware.seo_url seo 
LEFT JOIN product p ON p.id = seo.foreign_key 
WHERE p.active = '1'
GROUP BY seo_path_info 
HAVING COUNT(seo_other_column)>1 ;
    Login or Signup to reply.
  3. 0

    where caluse can’t be before group by or having clause. The following would be the correct order

    SELECT
FROM
JOIN
ON
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

    OR

    SELECT
FROM
JOIN
ON
WHERE
QUALIFY -- IF YOU ARE USING A WINDOWS FUNCTION INSTED OF GROUP BY
ORDER BY
LIMIT
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search