Joining the same table twice

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
TipPro
Forum Commoner
Posts: 35
Joined: Wed Mar 15, 2006 6:39 pm

Joining the same table twice

Post 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';
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Joining the same table twice

Post 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`;
TipPro
Forum Commoner
Posts: 35
Joined: Wed Mar 15, 2006 6:39 pm

Re: Joining the same table twice

Post by TipPro »

Yes, that makes sense. Thank you for your quick reply!
Post Reply