0

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
Improve this question
3
  • 1
    So what's going wrong? No rows? Too many rows? Error in SQL? Commented Jan 31, 2014 at 12:50
  • syntax error at #1064 - 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 Commented Jan 31, 2014 at 12:55
  • Ok - I've added that to your question Commented Jan 31, 2014 at 13:04

4 Answers 4

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

2 Comments

I got my query working perfectly here is my query after your guidance:
Ok, and finally i want to add "ORDER BY Rr.rating DESC" for this query how can i adapt.
0

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 ...
Improve this answer

Comments

0 
 (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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.