Trouble with joins

[xander85]

I'm having trouble properly constructing a join query. I have two tables:

 

TABLE `work_orders` (

  `id` int(6) NOT NULL default '0',

  `workid` int(3) NOT NULL default '0',

  `site_id` int(3) NOT NULL default '0',

  `invnum` int(5) NOT NULL default '0',

  `main` tinyint(1) NOT NULL default '0',

  `work_desc` varchar(255) NOT NULL default '',

  `work_date` date NOT NULL default '0000-00-00',

  `workdesc_inv` text NOT NULL,

  `task` tinyint(1) NOT NULL default '0',

  `part` tinyint(1) NOT NULL default '0',

  `qty` double NOT NULL default '0',

  `unitprice` double NOT NULL default '0',

  `discount` double NOT NULL default '0',

  `out` tinyint(1) NOT NULL default '0',

  `mailed` tinyint(1) NOT NULL default '0',

)

 

TABLE `mileage` (

  `mileid` int(12) NOT NULL auto_increment,

  `id` int(4) NOT NULL default '0',

  `date` date NOT NULL default '0000-00-00',

  `workid` int(4) NOT NULL default '0',

  `site_id` int(4) NOT NULL default '0',

  `dist` decimal(10,1) NOT NULL default '0.0',

  `purpose` text NOT NULL,

  `parking` double NOT NULL default '0',

  `billed` tinyint(1) NOT NULL default '0',

  UNIQUE KEY `mileid` (`mileid`)

)

 

I am using the following query:

 

select concat(work_orders.id, ' - ', work_orders.workid) AS 'ID & Work ID', work_orders.work_date, concat(customers.first_name, ' ', customers.last_name) AS name, work_orders.invnum, (work_orders.qty * work_orders.unitprice  ) AS servicefee FROM work_orders
JOIN customers ON work_orders.id = customers.id 
JOIN ((select * from mileage) As mileagetable)
GROUP BY work_orders.id, work_orders.workid
ORDER BY work_orders.id, work_orders.workid

 

This works great and will add up the invoice total correctly.

 

I am using this query as well:

SELECT sum( dist * 0.485 ) AS mileagefee
FROM `mileage`
GROUP BY id, workid
ORDER BY id, workid

 

This works great and selects the properly value.

 

However, I want to put these two queries together. However, when I try to add the "mileage query" using this query:

 

select concat(work_orders.id, ' - ', work_orders.workid) AS 'ID & Work ID', work_orders.work_date, concat(customers.first_name, ' ', customers.last_name) AS name, work_orders.invnum, sum(work_orders.qty * work_orders.unitprice) AS servicefee, SUM(mileage.dist * 0.485) AS mileagefees FROM work_orders
LEFT JOIN mileage ON work_orders.id = mileage.id AND work_orders.workid = mileage.workid
LEFT JOIN customers ON work_orders.id = customers.id GROUP BY work_orders.id
ORDER BY work_orders.id, work_orders.workid

 

It seems the problem is because the number of rows being summed for the service fee total and mileage total are not always the same. This is where my problem lies.

 

PLEASE HELP!

 

[fenway]

Define "put together".

[xander85]

I would like both queries to be executed at once. Meaning each resulting row in the query would display the servicefee and mileagefee total for each invoice as a seperate column.

 

I want the two queries that work fine alone to be combined.

[fenway]

Can you post the output for each individual query?

[xander85]

select concat(work_orders.id, ' - ', work_orders.workid) AS 'ID & Work ID', work_orders.work_date, concat(customers.first_name, ' ', customers.last_name) AS name, work_orders.invnum, SUM(work_orders.qty * work_orders.unitprice  ) AS servicefee FROM work_orders
JOIN customers ON work_orders.id = customers.id 
GROUP BY work_orders.id, work_orders.workid
ORDER BY work_orders.id, work_orders.workid

Results in (first few rows):

ID & Work ID  |   work_date  |   name  |  invnum  |   servicefee

1 - 1 2006-11-22 Luanne 1073 525

2 - 2 2007-10-29 Mike  1178 75

2 - 3 2007-11-27 Mike  1184 75

3 - 1 0000-00-00 Kris  1082 0

4 - 1 0000-00-00 Terry  1083 0

5 - 1 0000-00-00 Mary Beth  1084 0

6 - 1 0000-00-00 Joe  1085 0

7 - 1 2007-01-20 Bill 1074 487.5

 

SELECT id, workid, sum( dist * 0.485 ) AS mileagefee
FROM `mileage`
GROUP BY id, workid
ORDER BY id, workid

Results in the following:

id  |  workid  |   mileagefee

1 1 124.160

2 2 5.141

2 3 5.141

7 1 82.450

7 2 15.374

7 3 18.527

7 4 18.527

7 5 12.804

7 6 12.804

 

Basically, I want to add the right column (mileagefee) to the first query so in one query I know the servicefee and mileagefee.

 

Thanks for your help.

 

[xander85]

bump ^^

[xander85]

^^

[fenway]

First, you don't need the ORDER BY, since GROUP BY does this implicitly.

 

Second, one way to do this is to join these derived tables on id and work id... otherwise, you can issue a scalar subquery.

[xander85]

Can you give me an example? That would be greatly appreciated.

[fenway]

Add

 

( SELECT sum( dist * 0.485 ) AS mileagefee FROM `mileage` WHERE id = <the outer table id> and work_id = <the outer table work id ) ) AS mileageSum

 

To the first query.

[xander85]

I tried this:

 

select concat(work_orders.id, ' - ', work_orders.workid) AS 'ID & Work ID', work_orders.work_date, concat(customers.first_name, ' ', customers.last_name) AS name, work_orders.invnum, SUM(work_orders.qty * work_orders.unitprice  ) AS servicefee FROM work_orders
JOIN customers ON work_orders.id = customers.id 
JOIN ( SELECT sum( dist * 0.485 ) AS mileagefee FROM `mileage` WHERE id = work_orders.id and work_id = work_orders.work_id ) ) AS mileageSum)
GROUP BY work_orders.id, work_orders.workid

 

and it gives me an error #1064. I guess I'm confused and did not do this properly. thanks for the help!

[fenway]

You need to add this to your column list, not JOIN it in.