Need to write a complex compound query not sure how to start...

[Praetorian46]

 

---

 

Here is all of the info first

 

---

 

MySQL Server version: 5.0.27

MySQL client version: 5.0.27

 

+------------------------+
| Tables_in_Job_Tracking |
+------------------------+
| Customer_Dir           |
| Employee_Dir           |
| Job_Class              |
| Job_Dir                |
| Jobs                   |
+------------------------+

+-----------------------------+
|        Customer_Dir         |
+-------------+---------------+
| Customer_ID | Customer_Name |
+-------------+---------------+
|           1 | Customer1     |
|           2 | Customer2     |
|           3 | Customer3     |
+-------------+---------------+

+-----------------------------------------------------------------+
|                          Employee_Dir                           |
+-------------+---------------+-------------------+---------------+
| Employee_ID | Employee_Name | Employee_Password | Employee_Type |
+-------------+---------------+-------------------+---------------+
|           1 | Employee1     | CrypsPass1        |		0 |
|           2 | Employee2     | CrypsPass2        |             0 |
|           3 | Employee3     | CrypsPass3        |             0 |
+-------------+---------------+-------------------+---------------+

+----------+-------------+
|       Job_Class        |
+----------+-------------+
| Class_ID | Class_Name  |
+----------+-------------+
|        1 | Class1      |
|        2 | Class2      |
|        3 | Class3      |
+----------+-------------+

+---------------------------------+
|            Job_Dir              |
+--------+-------------+----------+
| Job_ID | Customer_ID | Job_Name |
+--------+-------------+----------+
|      1 |           1 | 07-0001  |
|      2 |           1 | 07-0002  |
|      3 |           2 | 07-0003  |
|      4 |           3 | 07-0004  |
+--------+-------------+----------+

+-------------------------------------------------------------------------+
|                                   Jobs                                  |
+----+--------+-------------+-------------+----------+-------+------------+
| ID | Job_ID | Customer_ID | Employee_ID | Class_ID | Hours | Date       |
+----+--------+-------------+-------------+----------+-------+------------+
|  1 |      1 |           1 |           1 |        1 |     1 | 2007-01-01 |
|  2 |      2 |           1 |           2 |        2 |     2 | 2007-01-02 |
|  3 |      3 |           2 |           3 |        3 |     3 | 2007-01-03 |
+----+--------+-------------+-------------+----------+-------+------------+

 

There is the DB Structure and some fake data...

 

$query = "SELECT * FROM Jobs WHERE Date BETWEEN '$SDate' AND '$EDate'";
if($Cust_ID!="") $query.= " AND Customer_ID IN ($Cust_ID)";
if($Job_ID!="") $query.= " AND Job_ID IN ($Job_ID)";
if($Empl_ID!="") $query.= " AND Employee_ID IN ($Empl_ID)";
if($Class_ID!="") $query.= " AND Class_ID IN ($Class_ID)";

 

There is the PHP code that I'm using...

 

SELECT * FROM Jobs 
WHERE Date BETWEEN '2007-01-01' AND '2007-01-31' 
AND Customer_ID IN (1,2) 
AND Job_ID IN (2,3) 
AND Employee_ID IN (2,1) 
AND Class_ID IN (1,2)

 

That is the query that is Generated by the code if I select certain things in the PHP Form...

+----+--------+-------------+-------------+----------+-------+------------+
| ID | Job_ID | Customer_ID | Employee_ID | Class_ID | Hours | Date       |
+----+--------+-------------+-------------+----------+-------+------------+
|  2 |      2 |           1 |           2 |        2 |     2 | 2007-01-02 |
+----+--------+-------------+-------------+----------+-------+------------+

 

That is what the query returns...

 

It all works great.  but what I need to do is write a query that will return the same out put but exchange the Field_IDs the the Field_Names.  So it would look something like this...

 

+----------+---------------+---------------+------------+-------+------------+
| Job_Name | Customer_Name | Employee_Name | Class_Name | Hours | Date       |
+----------+---------------+---------------+------------+-------+------------+
| 07-0002  | Customer1     |   Employee2   | Class2     | 2     | 2007-01-02 |
+----------+---------------+---------------+------------+-------+------------+

 

Any one have a clue of a way to do this?  ???  ???  ???  ???

 

Thank you for your time!

-Cody

[roopurt18]

User INNER JOINs:

http://dev.mysql.com/doc/refman/5.0/en/join.html

 

There are sample queries provided there.

[Praetorian46]

User INNER JOINs:

http://dev.mysql.com/doc/refman/5.0/en/join.html

 

There are sample queries provided there.

 

Thank you so very much for pointing me into the right direction... here is the end result and it works fast a beautifully!

 

SELECT CD.Customer_Name, JD.Job_Name, ED.Employee_Name, JC.Class_Name, J.Hours, J.Date
FROM Jobs J
LEFT JOIN (Customer_Dir CD, Employee_Dir ED, Job_Class JC, Job_Dir JD) 
ON ( CD.Customer_ID = J.Customer_ID
AND ED.Employee_ID = J.Employee_ID
AND JC.Class_ID = J.Class_ID
AND JD.Job_ID = J.Job_ID )
WHERE J.Date BETWEEN '2007-01-01' AND '2007-01-31'
AND J.Customer_ID IN (1,2) 
AND J.Job_ID IN (1,2) 
AND J.Employee_ID IN (1,2) 
AND J.Class_ID IN (1,2)

[roopurt18]

Unless you're users are allowed to delete records from customer_dir, employee_dir, job-class, and job_dir, I'm not sure why you'd be using LEFT JOIN.

 

But if it works it works.