MYSQL get other table data in a join

Question

Hello,

I am currently running this SQL

SELECT jm_recipe.name, jm_recipe.slug
FROM jm_recipe
LEFT JOIN jm_category_recipe ON jm_category_recipe.recipe_id = jm_recipe.id
WHERE jm_category_recipe.category_id = $cat"

This returns the desired results except that I also need to return the name of the category that the recipe I am looking for is in, to do this I tried to add the field in to my SELECT statement and also add the table into the FROM clause,

SELECT jm_recipe.name, jm_recipe.slug, jm_category_name
FROM jm_recipe, jm_category
LEFT JOIN jm_category_recipe ON jm_category_recipe.recipe_id = jm_recipe.id
WHERE jm_category_recipe.category_id = $cat"

However this just returns no results, what am i doing wrong?

Answer 1

You need to join both tables:

SELECT jm_recipe.name, jm_recipe.slug, jm.category_name
FROM jm_recipe 
    INNER JOIN jm_category_recipe ON jm_category_recipe.recipe_id = jm_recipe.id
    INNER JOIN jm_category ON jm_recipe.recipe_id = jm_category.recipe_id
WHERE jm_category_recipe.category_id = $cat

I've changed the joins to inner joins as well. You might want to make them both LEFT joins if you have NULLs and want them in the result.

Also, you're vulnerable to SQL Injection by simply copying over $cat.

Here's some PHP specific info for you (I'm assuming you're using PHP.)