2. PHPRunner General questions
  3. Inner Join Problem
This topic is locked

Inner Join Problem

 		8/9/2007 8:38:50 PM
PHPRunner General questions
C
 Christopher author

In my main table, I have three separate columns that have a numerical value for their category. A separate table holds the description associated with that number.
Essentially I need to join the same column in the category table to three different fields in the main database.
I tried this:
SELECT

posts_table.post_date,

posts_table.approved,

posts_table.`2` AS JobTitle,

posts_table.`5` AS JobDescription,

posts_table.`8` AS PostedBy,

posts_table.`6` AS JobClassification,

posts_table.`13` AS State,

posts_table.`14` AS JobType,

posts_table.`15` AS Location,

categories.category_id,

categories.category_name

FROM

posts_table

Inner Join categories ON (posts_table.`6` = categories.category_id OR posts_table.`5` = categories.category_id OR posts_table.`14` = categories.category_id)
but only the first field displays the value instead of the number.
Greatly appreciate the help I am dead in the water at this point.
J
 		Jane 8/10/2007

Hi,
try to use following SQL query:

SELECT

posts_table.post_date,

posts_table.approved,

posts_table.`2` AS JobTitle,

posts_table.`5` AS JobDescription,

posts_table.`8` AS PostedBy,

posts_table.`6` AS JobClassification,

posts_table.`13` AS State,

posts_table.`14` AS JobType,

posts_table.`15` AS Location,

categories.category_id,

categories.category_name as JobClassification2,

categories.category_name as JobDescription2,

categories.category_name as JobType2

FROM

posts_table

Inner Join categories ON (posts_table.`6` = categories.category_id OR posts_table.`5` = categories.category_id OR posts_table.`14` = categories.category_id)
C
 		Christopher author 8/10/2007

Jane, thanks and one problem. Instead of 607 records, I cam getting 1224, and on each record the category columns contain the same value, like Full time Full time Full time and then further down is the same record with Arizona Arizona Arizona.
My goal is to have one record with Full Time, Arizona, Administration for example.
F
 		frocco 8/10/2007

I think this might work:

SELECT

posts_table.id,

posts_table.post_date,

posts_table.approved,

c1.category_name AS category_Title,

c2.category_name AS category_Description,

c3.category_name AS category_Postedby,

c4.category_name AS category_Classification,

c5.category_name AS category_State,

c6.category_name AS category_JobType,

c7.category_name AS category_Location

FROM

posts_table

LEFT JOIN categories c1 ON posts_table.`2` = c1.category_id

LEFT JOIN categories c2 ON posts_table.`5` = c2.category_id

LEFT JOIN categories c3 ON posts_table.`8` = c3.category_id

LEFT JOIN categories c4 ON posts_table.`6` = c4.category_id

LEFT JOIN categories c5 ON posts_table.`13` = c5.category_id

LEFT JOIN categories c6 ON posts_table.`14` = c6.category_id

LEFT JOIN categories c7 ON posts_table.`15` = c7.category_id


HTH
Frank

In my main table, I have three separate columns that have a numerical value for their category. A separate table holds the description associated with that number.

Essentially I need to join the same column in the category table to three different fields in the main database.
I tried this:
SELECT

posts_table.post_date,

posts_table.approved,

posts_table.`2` AS JobTitle,

posts_table.`5` AS JobDescription,

posts_table.`8` AS PostedBy,

posts_table.`6` AS JobClassification,

posts_table.`13` AS State,

posts_table.`14` AS JobType,

posts_table.`15` AS Location,

categories.category_id,

categories.category_name

FROM

posts_table

Inner Join categories ON (posts_table.`6` = categories.category_id OR posts_table.`5` = categories.category_id OR posts_table.`14` = categories.category_id)
but only the first field displays the value instead of the number.
Greatly appreciate the help I am dead in the water at this point.
C
 		Christopher author 8/10/2007

Frank you are a genius!!!! Thank you so much! <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=20329&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />
F
 		frocco 8/10/2007

Glad I could help.
Regards,
Frank