Type of JOIN

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

From stackoverflow

• 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.

  Coughlin : Yes...every project WILL have some type of category it belongs too.

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

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

  That should work.

  Coughlin : And to display the cat_title, I can use: '.$row['cat_title'].' I tried that and get a blank output, with no errors

  Simon Hartcher : Run the query in a query analyser like phpMyAdmin and see if you get any results. If not, you might not have any data for some reason, otherwise you're probably reading the field in php incorrectly.

• 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
  

  Coughlin : That seemed to get my data to pull, but not the category. Any thoughts?

  robsymonds : Are you sure that each project has a project_category associated with it? Maybe you could post your table schemas (desc project, desc project_category are the commands I think for MySQL) and a sample row of data.

  Coughlin : The table PROJECT_CATEGORY as TWO fields (cat_id,cat_title), an example is: 1,E-Commerce On my PROJECTS table I have cat_id for there is a relationship there, so I am trying to display the CAT_TITLE instead of the CAT_ID

  robsymonds : If you run the query through phpMyAdmin or MySQL Query, are you seeing the full results with the category? If so, it could be a problem with the way you're trying to reference the field in PHP.

• 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);