SQL 1064 Error, Syntax going unseen

Question

SELECT
    i.*,
    ii.file_location
    FROM group_shop_item i, group_shop_itemimage ii, group_shop_brand b 
    WHERE 
    i.brand_id = b.id
    AND 
    b.brand_status_id = 1
    AND 
    i.is_deleted = 0
    AND 
    i.is_displayed = 1
    AND
    i.id = ii.item_id
    AND 
    ii.is_main = 1
    AND 
    i.deal_participate = 1 
    AND  
    i.brand_label_id IS NOT NULL
 ORDER BY i.datetime_modified DESC;

This SQL query keeps throwing me a 1064. It seems to be on the last line which I've tried with and without the i table variable. I can't for the life of me catch the error, anyone can lend me another pair of eyes?

I'm throwing this as a RAW query into the in built Django function and building this query with string concatenation. This copy paste is directly from a print I've done from the command line. It's outputting neatly but isn't reading when I run the view on my browser.

score 3 · Answer 1 · answered Dec 02 '17 at 00:57

3

Over 25 years ago ANSI Standard join syntax was adopted. You need to cease using comas between table names in the from clause.

SELECT
      i.*
    , ii.file_location
FROM group_shop_item i
INNER JOIN group_shop_itemimage ii ON i.id = ii.item_id
INNER JOIN group_shop_brand b ON i.brand_id = b.id
WHERE i.is_deleted = 0
AND i.is_displayed = 1
AND ii.is_main = 1
AND i.deal_participate = 1
AND i.brand_label_id IS NOT NULL
AND b.brand_status_id = 1
;

Regarding the 1064 error, please read this without the exact error message and the exact/full query we can't offer much insight into that.

The other thing you need to be careful of is that "select *" isn't good practice either.

answered Dec 02 '17 at 00:57

Paul Maxwell

33,002
3
32
51

Probably worth mentioning also that "building this query with string concatenation. " is generally considered an unsafe structure - or at least requires considerable care about where the strings are coming from so you don't end up with SQL injection (etc.) issues. – GregHNZ Dec 02 '17 at 01:19
@GregHNZ good comment! too true (*and the concatenation may very well be the syntax error source). – Paul Maxwell Dec 02 '17 at 01:26

SQL 1064 Error, Syntax going unseen

1 Answers1