I have 2 tables that I’d like to perform a combo inner join/left join on and can’t quite get the syntax correct.
My first join is:
SELECT questions.text,assignments_questions.question_id
FROM questions
INNER JOIN assignments_questions ON questions.question_id =assignments_questions.question_id
WHERE assignment_id=208
Then, in a second table, I have question_id’s and submitted_solutions. There may or may not be a submitted solution: regardless the result should show up so that I need a left join. In other words, my final result could look like:
Text ID Submitted solution
A 1 True
B 2
C 3 Blah Blah
Any help in getting the two joins to work together would be appreciated.
Thanks for such a quick response! It’s almost what I’m looking for. Is there any way to add an “AND” statement so that it ONLY applies to the 2nd LEFT JOIN table. For example, there could be many users submitting questions. If I could add “AND submitted_homework.user_id = 61” so that it only applies to the LEFT JOIN piece that would do it. Right now, if I write AND submitted_homework.user_id = 61, then it only produces the text for questions submitted by user_id 61. I’d actually like all of the question text for a given assignment, by a given user regardless of whether that user submitted a solution.
Here’s the current state of things:
SELECT questions.text
, assignments_questions.question_id
, submitted_homework.submitted_solution
FROM questions
INNER JOIN assignments_questions ON questions.question_id = assignments_questions.question_id
LEFT JOIN submitted_homework ON questions.question_id = submitted_homework.question_id
WHERE assignments_questions.assignment_id = 208
SELECT questions.text
, assignments_questions.question_id
, submitted_homework.submitted_solution
FROM questions
INNER JOIN assignments_questions ON questions.question_id = assignments_questions.question_id
LEFT JOIN submitted_homework ON questions.question_id = submitted_homework.question_id AND submitted_homework.user_id = 61
WHERE assignments_questions.assignment_id = 208
then it’s a good idea to start off your FROM clause with that table –
FROM assignments_questions
INNER
JOIN questions
ON questions.question_id = assignments_questions.question_id
now, the mysql optimizer is pretty smart, and for inner joins, it’s gonna figure out that the fastest results will be obtained by
retrieve only the assignments_questions rows for the given assignment, then join to the questions table get the related questions
rather than
retireve all questions, join to all matching assignments_questions rows, then throw away the ones that aren’t for assignment 208
see the difference?
like i said, mysql is smart enough to figure out that it should execute 1 instead of 2 (when it’s an inner join)
with this in mind, i find that it makes ~way~ more sense to actually write the FROM clause the same way
why?
because if you are looking at a strange query, trying to understand what it’s doing (and often the author of the query is usually yourself, and you’re trying to enhance it a couple months down the road), it’s a ~lot~ easier to figure it out if it’s written in such a way as to model what the query is actually trying to accomplish
i call the first table in the FROM clause the “driving” table, as it is what drives the successful and efficient retrieval of rows, and it is almost always the one that has the associated WHERE condition
