Queries and Relationships

[ironman]

Hello everyone. I’ve been working on some general queries for the past few hours, but I’m pretty new to PHP/SQL and having some difficulty. I’ve created two tables within my database that I will run my queries from.

 

Im using MySQL 5.0 through PHPMyAdmin.

 

 

The first table has the following columns.

 

Table “Master”

Activity # 

Due 

Account # 

Owner 

Order # 

Service Region 

Status 

Reason 

Type 

SR Sub-Area 

Tech Instructions 

Company 

Driving Directions 

SR # 

VIP 

Repeat Service Flag 

Dwelling 

Cancelled 

Customer wants DPP 

Related SR 

Create QA 

QA Generated 

OMS Order Id 

Modified Activity # 

Source QA Activity 

 

 

The second table has the following columns

 

Table “Supervisor”

Last_Name 

First_Name 

User_ID 

Service_Region 

Tech_Team 

Tech_Supervisor

 

 

The “Owner” in table master is the same as “User_ID” in table supervisor. So I can associate which “Owner” is on what “Tech_Team” or which “Owner” is under which “Tech_Supervisor” ect.

 

I would like to generate the following query:

 

(Breaking up the teams by statistics)

• Team

o Total Jobs Total amount of “Order # “ by each “Owner” by each  “tech_team”

o Completion %  (“status” = “closed”) / (total number of order # by tech_team)

o Cancelled (total number of status =  canceled by tech_team)

o Closed (total number of status =  closed by tech_team)

o Hold (total number of status =  hold by tech_team)

o Past Open      (total number of status =  past open by tech_team)

o On Site          (total number of status =  on site by tech_team)

 

 

The final report would look something like this:

 

Total Jobs Comp % Cancelled Closed Hold Past Open On Site

Team 3A 57 71.93% 16 41 1 1 1

Team 3B 27 85.19% 4 23 0 0 0

Team 4A 28 71.43% 6 20 2 2 2

Team 4B 52 67.31% 16 35 3 3 3

Team 5A 45 57.78% 17 26 2 2 2

Team 5B 0 #DIV/0! 0 0 0 0 0

Total Jobs 209 69.38% 59 145 8 8 8

 

 

 

So I know I need to first associate both tables together. Then comes the hard part of writing the code to do the actual query. This seems like a job for Superman. If anyone can find the time to assist, I’d appreciate it greatly!

 

 

 

 

 

 

 

[PC Nerd]

From my knowledge ( as im not great at relationship queryies) - you need to specify each table and then use the fields like this:

 

SELET table1.field1, table1.field2, table2.field3 FROM table1, table2 WHERE<condition>

 

now i think there is an INNER JOIN statement there however that may have to comebefore this query.....

gldk

 

[PC Nerd]

 

 

 

edit:

 

normal syntax is as above:  TABLENAME.FIELDNAME, TABLENAME.FIELDNAME etc thats the same as in the WHER cluase as well

 

however if you are using a join that is along the lines of "all from one table and records from the second where they match"... you need to use:

 

table_name {RIGHT|LEFT|FULL} [OUTER] JOIN table_name {ON <condition> | USING (column_name [,column_name]) }

hopefully that makes sense

 

thats in teh context of:

 

SELECT <fields> FROM table_name LEFT OUTER JOIN ON tbl1.fiield1=tbl2.field1 WHERE <another condition>

 

hope that helps

 

[ironman]

Yeah that helped. I'll play around with it for a bit and repost if I have more questions. Thanks a lot!