Help with Join In Mysql Query

[KenBZim]

The following query sums sales into foreign or local depending upon the ROUTE_ID found in the trips table.
Sales are determined as transactions in the transactions table where the revexpcode is classified as Type 'R'(For Revenue)


select
`revexpcodes`.`RevExpCode` AS `RevExpCode`,
`revexpcodes`.`Description` AS `Description`,
sum(if(((`trips`.`ROUTE_ID` = 1) and (`revexpcodes`.`Type` = _latin1'R')),`transactions`.`Amount`,0)) AS `LOCALSALES`,
sum(if(((`trips`.`ROUTE_ID` = 2) and (`revexpcodes`.`Type` = _latin1'R')),`transactions`.`Amount`,0)) AS `FOREIGNSALES`,
sum(if(((`trips`.`ROUTE_ID` > 0) and (`revexpcodes`.`Type` = _latin1'R')),`transactions`.`Amount`,0)) AS `TOTALSALES`
from
(((`transactions` join `revexpcodes` on((`transactions`.`RevExpCode` = `revexpcodes`.`RevExpCode`))) join `tickets` on((`tickets`.`Ticket` = `transactions`.`TRReference`))) join `trips` on((`tickets`.`Trip` = `trips`.`Trip`)))
where
((`transactions`.`CurrencyCode` = _latin1'ZAR') and (`transactions`.`TRDate` between _utf8'2009-01-01 00:00:00' and _utf8'2009-12-31 23:59:59'))
group by
`revexpcodes`.`RevExpCode`,`revexpcodes`.`Description`

THE ABOVE QUERY WORKS FINE BUT IT IS MISSING SOMETHING:
THERE IS ALSO A 'TRIPNO' FIELD IN THE TRANSACTIONS TABLE i.e. Transactions should be summed by the join transactions-->>Tickets-->>Trips-->>Routes
(As shown in the above query)
OR in some cases the transaction line itself may have a trip number i.e. transactions.trip=trips.trip - This is not common but does happen.

The reason for this is that 90% of sales or revenue is derived from ticket sales thus the join transactions-->Tickets-->Trips-->Routes.ROUTE_ID
However there are instances of revenue where the transaction in the transactions table has nothing to do with ticket sales but is still
related to that trip, a good example of this would be passenger luggage where a single transaction line in the transactions table would represent
a total of passenger luggage for that trip, in this case the record in the transactions table would have a value in the transactions.TripNo Field
to show which trip this transaction belongs.(For ticket sales the transactions.tripNo would be blank because the trip number would be derived from
the tickets.Trip field)

I therefore need to add an addtional Join in the from Clause that also creates a join between the transactions table and the trips table
When I try this I have added the join at the end of the clause as a left join)

from
`transactions` join `revexpcodes` on`transactions`.`RevExpCode` = `revexpcodes`.`RevExpCode` join `tickets` on`tickets`.`Ticket` = `transactions`.`TRReference` join `trips` on`tickets`.`Trip` = `trips`.`Trip` Left Join trips on `transactions`.`Trip`=`trips`.`Trip`

I get the error "Not Unique Table/Alias 'Trips'"

I hope that this makes sense and i really hope that someone can help, I am under pressure to get this query to work.

Thanks in advance.

[si_the_geek]

If you want to refer to a more than one 'copy' of a table, you need (as the error message implied) separate aliases for each of them.

To use an alias just add what you want to call it after the table name (with or without the keyword 'as'), and everywhere else use that name rather than the original, eg:

Left Join trips as Trips2 on `transactions`.`Trip`= Trips2.`Trip`

I recommend doing this for both of them, as that way you will get an error if you forget to use the alias somewhere.

[KenBZim]

Thanks I will give that a try