CODEWITHSUNDEEP
mysql
Vasanth
Vasanth

Reputation: 37

SQL syntax error when using inner joins in query

Working query 

(   SELECT R.ref as ref, 
           R.field8, 
           R.file_extension, 
           DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, 
           Ra.rank as rank 
    FROM resource R 
    INNER  JOIN resource_ranking Ra ON R.ref = Ra.ref_id 
    ORDER BY Ra.rank limit 2
) 
UNION ALL
(   SELECT R.ref as ref, 
           R.field8, 
           R.file_extension, 
           DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, 
           '99' as rank  
    FROM resource R
    LEFT JOIN (
           SELECT ref_id 
           FROM resource_ranking 
           ORDER BY rank LIMIT 2
    ) AS B ON R.ref = B.ref_id
    INNER JOIN collection_resource C ON R.ref = C.resource
    WHERE B.ref_id IS NULL 
      AND R.ref !=  '-1' AND C.collection = 1 
)
ORDER BY rank ASC, ref DESC LIMIT 0,  8

the above query is working fine i wants to add another table to existing query using inner joins

Below is my written query using inner joins

(   SELECT R.ref as ref, 
           R.field8, 
           R.file_extension, 
           DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, 
           Ra.rank as rank 
    FROM resource R 
    INNER  JOIN resource_ranking Ra ON R.ref = Ra.ref_id 
    ORDER BY Ra.rank limit 2
    INNER  JOIN resource_rating Rr ON R.ref = Rr.resource_id 
    ORDER BY Rr.rating DESC
) 
UNION ALL
(   SELECT R.ref as ref, 
           R.field8, 
           R.file_extension, 
           DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, 
           '99' as rank  
    FROM resource R
    LEFT JOIN (
         SELECT ref_id 
         FROM resource_ranking 
         ORDER BY rank LIMIT 2
    ) AS B ON R.ref = B.ref_id
    INNER JOIN collection_resource C ON R.ref = C.resource
    WHERE B.ref_id IS NULL AND Rr.userid=1 
      AND R.ref !=  '-1' AND C.collection = 1
)
ORDER BY rank ASC, ref DESC LIMIT 0, 8

However, this gives the following 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 'INNER JOIN resource_rating Rr ON R.ref = Rr.resource_id ORDER BY Rr.rating DESC' at line 3

Upvotes: 0

Views: 1687

Answers (4)

Vasanth
Vasanth

Reputation: 37

(SELECT R.ref as ref, R.field8, R.file_extension, Rr.rating, DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, Ra.rank as rank FROM resource R 
    INNER  JOIN resource_ranking Ra ON R.ref = Ra.ref_id 
    INNER JOIN resource_rating Rr ON R.ref = Rr.resource_id
ORDER BY Ra.rank limit 2) 
UNION ALL
    (SELECT R.ref as ref, R.field8, R.file_extension, Rr.rating, DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, '99' as rank  FROM resource R
    LEFT JOIN (SELECT ref_id FROM resource_ranking ORDER BY rank LIMIT 2) AS B ON R.ref = B.ref_id
    INNER JOIN collection_resource C ON R.ref = C.resource
    INNER JOIN resource_rating Rr ON R.ref = Rr.resource_id
    WHERE B.ref_id IS NULL AND Rr.user_id = 1 
    AND R.ref !=  '-1' AND C.collection = 1)
    ORDER BY rank ASC, ref DESC LIMIT 0, 8

Upvotes: 0

krokodilko
krokodilko

Reputation: 36087

The error is here:

INNER  JOIN resource_ranking Ra ON R.ref = Ra.ref_id 
ORDER BY Ra.rank limit 2
INNER  JOIN resource_rating Rr ON R.ref = Rr.resource_id 
ORDER BY Rr.rating DESC

The SELECT statement can have only one ORDER BY clause, and it must be the last clause in the statement (ORDER BY must be at the end):

SELECT ....
FROM table1
JOIN table2 ON ....
JOIN ......
WHERE ...
ORDER BY ...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This is your first subquery:

SELECT R.ref as ref, R.field8, R.file_extension,
       DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, Ra.rank as rank
FROM resource R  INNER JOIN
     resource_ranking Ra
     ON R.ref = Ra.ref_id
ORDER BY Ra.rank
limit 2
INNER  JOIN resource_rating Rr ON R.ref = Rr.resource_id ORDER BY Rr.rating DESC)

The inner join is in the wrong place. Try this:

SELECT R.ref as ref, R.field8, R.file_extension,
       DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, Ra.rank as rank
FROM resource R  INNER JOIN
     resource_ranking Ra
     ON R.ref = Ra.ref_id INNER JOIN
     resource_rating Rr
     ON R.ref = Rr.resource_id
ORDER BY Ra.rank
limit 2

I'm not sure what the additional order by clause is for.

If you learn to format your queries so they are readable, it will be easier for you to find syntax errors.

Upvotes: 1

Ruben Serrate
Ruben Serrate

Reputation: 2783

Try after getting rid of this:

 ORDER BY Ra.rank limit 2

If you only want two rows from the joins place the LIMIT 2 after the second one.

Upvotes: 0

Related Questions