Page 1 of 1

Problem with an INNER JOIN and multiple tables

Posted: Thu Feb 05, 2004 5:10 pm
by kanshou
I am trying to get a webcalendar to output multiple rows from a table that shares an id with another table's rows.
example:

event 1 - Rooms reserved 1, 2, 3

I can display for event 1 and room 1, but it wont display room 2 and 3. The multiple ids match event 1's id too..

ID Name
1 Event 1

ID Name
1 Room 1
1 Room 2
1 Room 3

My SQL query looks like this

Code: Select all

<?php
 $sql = "SELECT webcal_entry.cal_name, webcal_entry.cal_description, "
    . "webcal_entry.cal_date, webcal_entry.cal_time, "
    . "webcal_entry.cal_id, webcal_entry.cal_ext_for_id, "   
    . "webcal_entry.cal_priority, "   
    . "webcal_site_extras.cal_data, "
    . "webcal_entry.cal_access, webcal_entry.cal_duration, "
    . "webcal_entry_user.cal_status, "
    . "webcal_entry_user.cal_login ";
  if ( $want_repeated ) {
    $sql .= ", "
      . "webcal_entry_repeats.cal_type, webcal_entry_repeats.cal_end, "
      . "webcal_entry_repeats.cal_frequency, webcal_entry_repeats.cal_days "
      . "FROM webcal_entry, webcal_site_extras, webcal_entry_repeats, webcal_entry_user "
      . "INNER JOIN webcal_entry.cal_id = webcal_site_extras.cal_id WHERE "
      . "webcal_entry.cal_id = webcal_entry_repeats.cal_id AND ";
  } else {
    $sql .= "FROM webcal_entry, webcal_site_extras, webcal_entry_user INNERJOIN ";
  }
  $sql .= "webcal_entry.cal_id = webcal_site_extras.cal_id " .
   "WHERE webcal_entry.cal_id = webcal_entry_user.cal_id " .
    "AND webcal_entry_user.cal_status IN ('A','W') ";

?>
Now the problem is, I am getting a SQL error that looks like
Error
Error executing query: You have an error in your SQL syntax near '.cal_id WHERE webcal_entry.cal_id = webcal_entry_repeats.cal_id AND webcal_entry' at line 1


SELECT webcal_entry.cal_name, webcal_entry.cal_description, webcal_entry.cal_date, webcal_entry.cal_time, webcal_entry.cal_id, webcal_entry.cal_ext_for_id, webcal_entry.cal_priority, webcal_site_extras.cal_data, webcal_entry.cal_access, webcal_entry.cal_duration, webcal_entry_user.cal_status, webcal_entry_user.cal_login , webcal_entry_repeats.cal_type, webcal_entry_repeats.cal_end, webcal_entry_repeats.cal_frequency, webcal_entry_repeats.cal_days FROM webcal_entry, webcal_site_extras, webcal_entry_repeats, webcal_entry_user INNER JOIN webcal_entry.cal_id = webcal_site_extras.cal_id WHERE webcal_entry.cal_id = webcal_entry_repeats.cal_id AND webcal_entry.cal_id = webcal_site_extras.cal_id WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND webcal_entry_user.cal_status IN ('A','W') AND (webcal_entry_user.cal_login = 'mharper' OR webcal_entry_user.cal_login = '__public__' ) ORDER BY webcal_entry.cal_time, webcal_entry.cal_id
Can anyone tell me where I have gone wrong?
Thanks.

Posted: Thu Feb 05, 2004 5:22 pm
by Weirdan
You can't use WHERE keyword more then once in the query.

Posted: Thu Feb 05, 2004 5:35 pm
by kanshou
OK, I'm getting a sql error in a query, and I was hoping someone could tell me what is going wrong.

Code: Select all

<?php
$sql = "SELECT webcal_entry.cal_name, webcal_entry.cal_description, "
    . "webcal_entry.cal_date, webcal_entry.cal_time, "
    . "webcal_entry.cal_id, webcal_entry.cal_ext_for_id, "
    . "webcal_entry.cal_priority, "
    . "webcal_site_extras.cal_data, "
    . "webcal_entry.cal_access, webcal_entry.cal_duration, "
    . "webcal_entry_user.cal_status, "
    . "webcal_entry_user.cal_login ";
$sql .= "FROM webcal_entry LEFT JOIN webcal_site_extras ON webcal_entry.cal_id = webcal_site_extras.cal_id ";
    $sql .= "LEFT JOIN webcal_entry_user webcal_entry_user.cal_id = webcal_entry.cal_id) WHERE ";
  }
  $sql .= "webcal_entry_user.cal_id = webcal_entry.cal_id, webcal_entry_user.cal_status IN ('A','W') ";

?>
And the error I am getting is:
Error executing query: You have an error in your SQL syntax near '.cal_id = webcal_entry.cal_id) WHERE webcal_entry_user.cal_id = webcal_entry.cal' at line 1
SELECT webcal_entry.cal_name, webcal_entry.cal_description, webcal_entry.cal_date, webcal_entry.cal_time, webcal_entry.cal_id, webcal_entry.cal_ext_for_id, webcal_entry.cal_priority, webcal_site_extras.cal_data, webcal_entry.cal_access, webcal_entry.cal_duration, webcal_entry_user.cal_status, webcal_entry_user.cal_login FROM webcal_entry LEFT JOIN webcal_site_extras ON webcal_entry.cal_id = webcal_site_extras.cal_id LEFT JOIN webcal_entry_user webcal_entry_user.cal_id = webcal_entry.cal_id) WHERE webcal_entry_user.cal_id = webcal_entry.cal_id, webcal_entry_user.cal_status IN ('A','W') AND (webcal_entry_user.cal_login = 'rlowe' OR webcal_entry_user.cal_login = '__public__' ) ORDER BY webcal_entry.cal_time, webcal_entry.cal_id

Posted: Fri Feb 06, 2004 8:04 am
by Weirdan
You have unmatched closing brace there