Page 1 of 1

Joining the same table twice

Posted: Mon Nov 01, 2010 11:13 am
by TipPro
I have two tables I need to grab data from. The first table is the main order table and the second table lists the order charges.

I need rows that look like this...

OrderId, OrderDate, OrderTotal, TaxCharge, ShippingCharge

The first three fields all come from the main order table. The last two fields come from the order charges table. The complicated thing though is that the order charges table looks like this...

OrderId, ChargeType, ChargeAmount

so THE data would look like this...

123, TAX, 4.50
123, SHIPPING, 12.60
124, TAX, 3.45
124, SHIPPING, 11.75

I wrote a mySQL query that seems to work, but phpmyadmin gets confused and returns all the rows (correctly) but in the phpmyadmin header it states...

Showing rows 0 - 0 (1 total, Query took 0.0100 sec)

Is there something wrong with query - or - is there a better way of achieving what I need?

Code: Select all

SELECT `Order_main`.`OrderId`, `Order_main`.`OrderDate`, `Order_main`.`OrderTotal`, `SHIPPINGCHARGES`.`ChargeAmount` AS `ShippingCharge`, `TAXCHARGES`.`ChargeAmount` AS `TaxCharge`
FROM `Order_main`
LEFT JOIN `Order_charges` AS `SHIPPINGCHARGES`
ON `Order_main`.`OrderId`= `SHIPPINGCHARGES`.`OrderId`
LEFT JOIN `Order_charges` AS `TAXCHARGES`
ON `Order_main`.`OrderId`= `TAXCHARGES`.`OrderId`
WHERE `TAXCHARGES`.`ChargeType` = 'TAX' AND `SHIPPINGCHARGES`.`ChargeType` = 'SHIPPING';

Re: Joining the same table twice

Posted: Mon Nov 01, 2010 11:39 am
by Weirdan
Looks good to me (if tables are related 1:1), however this could be simplified to use just one join:

Code: Select all

SELECT 
   `Order_main`.`OrderId`, 
   `Order_main`.`OrderDate`, 
   `Order_main`.`OrderTotal`, 
   sum(`charges`.`ChargeAmount` * (`charges`.`ChargeType`='SHIPPING')) AS `ShippingCharge`,
   sum(`charges`.`ChargeAmount` * (`charges`.`ChargeType`='TAX')) AS `TaxCharge`
FROM `Order_main`
LEFT JOIN `Order_charges` AS `charges`
ON `Order_main`.`OrderId`= `charges`.`OrderId`
GROUP BY `Order_Main`.`OrderId`;

Re: Joining the same table twice

Posted: Mon Nov 01, 2010 11:55 am
by TipPro
Yes, that makes sense. Thank you for your quick reply!