Type of JOIN

Question

I have a table called PROJECT_CATEGORY and it contains two fields

cat_id and cat_title

I am storing the cat_id in another table called PROJECTS so:

project_category.cat_id = projects.cat_id

What is the best way using MySQL to run a query to show the cat_title. In other words I want to display the name not the ID number of the table. Would this work:

SELECT * FROM projects INNER JOIN projects ON project_category.cat_id = projects.cat_id

Then call it:

'.$row['cat_title'].'

Any thoughts?

Thanks,

Ryan

Answer 1

If every project has a valid cat_id and cat_id is unique, than INNER JOIN is fine.

If any project has a NULL cat_id, you want a LEFT JOIN.

If cat_id is not a unique field (primary key), you may want to use a subquery to limit yourself to one result per project.

Answer 2

SELECT
  projects.cat_id cat_id
FROM
  projects INNER JOIN
  project_category ON project_category.cat_id = projects.cat_id

Answer 3

SELECT cat_title 
FROM projects 
    INNER JOIN project_category ON project_category.cat_id = projects.cat_id

That should work.

Answer 4

Try not to use SELECT *, instead choose the specific fields you need. Also, if you use multiple tables in a query, alias them properly so you can pull out values as needed and not run into ambiguity problems.

Anyways, you could try something like:

SELECT       
 project_category.cat_title 
 ,projects.*  
FROM   
 projects   
  LEFT OUTER JOIN     
   project_category
    ON project_category.cat_id = projects.cat_id

Answer 5

Your join looks fine. FWIW, I'd use the USING clause in this case. It works the same, but it's a bit more clear and concise:

SELECT * FROM projects INNER JOIN project_category USING (cat_id);

If you're having trouble finding the column in your $row associative array, I'd check the spelling of your column definition, including capitalization. If you declared your table this way:

CREATE TABLE `Project_Category` (
  `Cat_ID`    INTEGER NOT NULL,
  `Cat_Title` VARCHAR(20) NOT NULL
);

Then the result set may use that literal spelling and capitalization in the array keys. PHP array keys are case-sensitive.

print $row['Cat_Title'];

You should probably dump the $row array to see what it thinks its keys are:

print_r($row);