multiple JOIN query
Posted: Sat May 03, 2008 3:46 pm
Getting into database normalization and it's just one big headache.
I'm working on a query for an appointment page. It takes data from 5 different tables.
This is the table structure
This is the php query that does not seem to work:
The query is supposed to return results for a date range ($weekDateLow[$d] to $weekDateHigh[$d]) and for one specific employee ($prEmployeeId[$i]).
Please note that more than one employee is assigned to each job. I'm only trying to retreive one employee's schedule even if he/she's assigned to do that job with someone else.
For some reason I'm gettin an error as follows:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource
I'm working on a query for an appointment page. It takes data from 5 different tables.
This is the table structure
Code: Select all
job |job_employee |employee |customer |customer_address
---------------+-------------+-------------+--------------+---------------
job_id(PK) |assign_id(PK)|emp_id(PK) |cust_id(PK) |address_id(PK)
customer_id(FK)|job_id(FK) |emp_firstname|cust_firstname|customer_id(FK)
address_id (FK)|employee_id |emp_lastname |cust_lastname |
job_date | | |special |
job_status | | | |Code: Select all
$sql = "SELECT job.job_id, job_employee.assign_id, job.job_status, job.job_date, job.address_id,
customer_address.cust_id, customer_address.google_address,
customer.cust_firstname, customer.cust_lastname, customer.special FROM job
INNER JOIN job_employee ON (job.job_id = job_employee.job_id AND job_employee.employee_id = '$prEmployeeId[$i]')
INNER JOIN customer_address ON (job.address_id = customer_address.address_id)
INNER JOIN customer ON (customer_address.customer_id = customer.cust_id)
WHERE (job.job_date >= '$weekDateLow[$d]' && job.job_date <= '$weekDateHigh[$d]') AND (job.job_status = 'D' || job.job_status = 'S') ORDER BY job.job_date";Please note that more than one employee is assigned to each job. I'm only trying to retreive one employee's schedule even if he/she's assigned to do that job with someone else.
For some reason I'm gettin an error as follows:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource