mySQL multiple inner joins

Question

I have two tables I'm pulling information from.

Lets say table1 has following columns (id, title, category, sub_category, sub_sub_category)

Lets say table2 has following columns (category_id, category_name)

I have a select statement that so far looks as follows:

SELECT
  table1.id,
  table1.title,
  table2.category_name as Cat1,
  table2.category_name as Cat2,
  table2.category_name as Cat3
FROM
  table1,
  table2
INNER JOIN table2 as c1 ON c1.category_id = table1.category
INNER JOIN table2 as c2 ON c2.category_id = table1.sub_category
INNER JOIN table2 as c3 ON c3.category_id = table1.sub_sub_category
WHERE
  table1.id = ?

This gives me an error about table1.category being an unknown column

I have also tried

SELECT
  table1.id,
  table1.title,
  table2.category_name as Cat1,
  table2.category_name as Cat2,
  table2.category_name as Cat3
FROM
  table1,
  table2
WHERE table1.id = ?
AND   table1.category = table2.category_id
AND   table1.sub_category = table2.category_id
AND   table1.sub_sub_category = table2.category_id

The last example at least gives me column output I'm looking for which would be

(table1.id, table1.title, table1.category name, table1.sub_category name...)

So showing the category name from table 2 instead of the ID's. I am an amateur coder and haven't had to use inner joins before but maybe that is what I need to do. I just can't figure out how to get it to output the data I need.

Thank you in advance for your time and consideration.

score 4 · Accepted Answer · answered Mar 14 '15 at 20:55

Your problem is that you have a comma in the from clause. Simple rule: never use commas in the from clause. Always use explicit join syntax.

Then, you also have table2 mentioned an extra time, and your select is pulling columns from the wrong instance of table2.

The fixed up query looks like:

SELECT t1.id, t1.title,
       c1.category_name as Cat1, c2.category_name as Cat2,
       c3.category_name as Cat3
FROM table1 t1 INNER JOIN
     table2 c1
     ON c1.category_id = t1.category INNER JOIN
     table2 c2
     ON c2.category_id = t1.sub_category INNER JOIN
     table2 c3
     ON c3.category_id = t1.sub_sub_category
WHERE t1.id = ?;

score -1 · Answer 2 · answered Mar 14 '15 at 20:52

Ups, you have a couple of problems here. First table2 can have only one column called category_name so there is no reason to select it three times like you do, result of that would be that you have three absolutely same column with different name.

Second the syntax is completely wrong. Syntax for INNER JOIN is

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

also you need to have column in both table usually foreign key and that column will be use to match data. I guess in your case it should be category_id from second table and in first table that's column you named category (it, should be renamed at category_id it's more convenient)...

So if understand right you want to select id, title from first table and category_name from second you could do that like this:

SELECT table1.id, table1.title, table2.category_name
FROM table1
INNER JOIN table2
ON table1.category_id = table2.category_id;

And friendly advice to you is to find some online sources and learn a little bit more about this before you continue with what ever you do...

Thank you for taking the time to post. Please realize I am an amateur and learning all self taught. I do alright to look up examples and reverse engineer them... but do far better with PHP than mySQL. The above statement works but it doesn't solve the issue of getting the name for multiple categories in one statement. I could get the name from table2 for a single field with a where clause. The problem I'm having is joining the name from table2 with 3 fields in table1. I know I need to give each column unique ID which is why it shows table2.category_name 3 times in the select statement. — Aaron, Mar 14 '15 at 21:03
You're welcome, I didn't understand well that in first table is stored category_id in columns category, sub_category etc form second table so I wasn't be much of a help... But I'm glad that Gordon solve your problem... I hope I would be more helpful next time :) GL... — Aleksandar Miladinovic, Mar 14 '15 at 21:43

mySQL multiple inner joins

2 Answers2