Query Multiple Tables with SELECT statement

[n1concepts]

Hi,

 

I need some help defining the correct SELECT statement that will pull specific information from several tables to echo desired results. Here are the tables and their associated fields (each confirmed with the correct syntax) defined in what I've attempted to query results:

 

$job_record = $_GET['id'];					      // pulls job id from super global 

$search="SELECT DISTINCT jobs.id, jobs.job_url,jobs.details,jobs.title,employer.company,industries1.industry,
jtypes.jobType,location.location FROM jobs,employer,industries1,jtypes,location
WHERE jobs.id = $job_record
AND jobs.industry1 = industries1.industry
AND jobs.job_types = jtypes.jobType
AND jobs.location = location.location
LIMIT 1;";

  //-run  the query against the mysql query function
  $search_results=mysql_query($search) or die("<br>Query string: $search_results<br>Returned error: " . mysql_error() );
  //-create  while loop and loop through result set
  while($row=mysql_fetch_array($search_results))
  {
    $detail_id = $row['id'];
    $jurl = $row['job_url'];
    $title = $row['title'];
    $details = $row['details'];
    $company = $row['company'];
    $indus1 = $row['industry'];
    $jtype = $row['jobType'];
    $location = $row['location'];


    echo "<strong>".$detail_id."</strong><br />";
    echo $title."</strong><br />";
    echo $company."<br />";
    echo $details."<br />";
    echo $indus1."<br />";
    echo $jtype."<br />";
    echo $location."<br />";
    echo $jurl;

 

Results: empty set.

 

Again, I have confirmed the table & field names are accurate and know the problem rest with how I'm trying to combine the (employer, industries1, jtypes, & location tables to the query to pull those associated records into the query to include with captured fields from the "jobs" table.

 

FYI: I did get the initial query to work when I defined this SELECT statement - only pulling the employer field and having just the jobs & employer info echo out in WHILE loop so I know that's close to what I need but I'm not sure how to add the remaining table into this statement to obtain those fields of information:

 

$search="SELECT DISTINCT jobs.id, jobs.job_url,jobs.details,jobs.title,employer.company FROM jobs left join employer on (employer.id = jobs.company ) where jobs.id = $job_record Limit 1;";

 

Any help to build the correct SELECT statement to pull everything from the other  tables (see the above "SELECT" statement to find all those tables & fields) is appreciated.

 

"I know it's just me not knowing the right SQL structure to place each of the statements in the WHERE clause".

 

thx,

Craig