Joining the same table twice
Posted: Mon Nov 01, 2010 11:13 am
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?
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';