CODEWITHSUNDEEP
mysql
user39980
user39980

Reputation:

Which type of join to use?

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?

Upvotes: 0

Views: 261

Answers (6)

msmafra
msmafra

Reputation: 1714

You can use NATURAL JOIN:

SELECT CAT_TITLE
FROM projects
NATURAL JOIN project_category

If the colunms are the same type and not null there is no need to put the fields in the join and it will work fine.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562240

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

Upvotes: 0

robsymonds
robsymonds

Reputation: 61

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

Upvotes: 0

Simon Hartcher
Simon Hartcher

Reputation: 3510

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

That should work.

Upvotes: 0

Loki
Loki

Reputation: 30920

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

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

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.

Upvotes: 1

Related Questions