[RESOLVED] MySQL syntax error

hi.. i am trying to do a INSERT, SELECT and inner join statement together and i received this error..

My SQL syntax

Code:

---

INSERT INTO TableSalesReportMU (AirWayBillNumber, FlightDate, DepartureFrom, Destination, Via1, GrossWt, ChargeableWt, Prepaid, TotalPrepaid, FreightCollect, Comm, DueAgent, Fuel, Security, Others, DueCarrier, FreightDue, Discount, CarrierRate, Airline, IATARate, DGR ) SELECT TableAWBDetails.AirWayBillNumber, TableAWBDetails.FlightDate, TableAWBDetails.DepartureFrom,TableAWBDetails.Destination, TableAWBDetails.Via1, TableAWBDetails.GrossWeight, TableAWBDetails.CostingChargeableWeight,
                TableAWBDetails.Prepaid, TableAWBDetails.TotalPrepaid, TableAWBDetails.FreightCollect, TableAWBDetails.Comm,
                TableAWBDetails.DueAgent, TableAWBDetails.Fuel, TableAWBDetails.Security, TableAWBDetails.Others,
                TableAWBDetails.DueCarrier, TableAWBDetails.FreightDue, TableAWBDetails.Discount, TableAWBDetails.CarrierRate
                TableAWB.Airline, TableAWBDetails.IATARate, TableAWBDetails.DGR FROM (TableAWB INNER JOIN TableAWBDetails ON TableAWB.AirWayBillNumber = TableAWBDetails.AirWayBillNumber)
                WHERE TableAWB.Airline='MU' and TableAWBDetails.Report = 'MU' AND (TableAWB.Costing <> 0 or TableAWBDetails.Destination = 'CNX')
AND TableAWBDetails.FirstHalf = True AND Month = 'June' AND Year = '2011'

---

error:

Code:

---

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near '.Airline, TableAWBDetails.IATARate, TableAWBDetails.DGR FROM (TableAWB INNER JOI' at line 5

---

Table Datatype:

TableAWB:
http://img825.imageshack.us/img825/1...bleairline.jpg

TableAWBDetails:

http://img402.imageshack.us/img402/9...awbdetail1.png

TableAWBDetails Cont:
http://img839.imageshack.us/img839/6...awbdetail2.png

TableSalesReportMU
http://img708.imageshack.us/img708/2...lemureport.png

Uploaded with ImageShack.us
i am using MySQL server 5.5

When you get an error with a long SQL statement, the best thing to do is format it so that you can read it properly (rather than being a few massively long lines).

Once you do that, the problem (and thus solution) should be reasonably easy to find - it is soon before the text that the error message showed you.

A simple sytax check failes in MS SQL and I guess it is the same for MYSQL. A comma is missing here:

TableAWBDetails.CarrierRate TableAWB.Airline,

A free tool to format sql is available here.
Using this tool, I was able to format your sql to a more readable format.

Code:

---

INSERT INTO tablesalesreportmu
            (airwaybillnumber,
            flightdate,
            departurefrom,
            destination,
            via1,
            grosswt,
            chargeablewt,
            prepaid,
            totalprepaid,
            freightcollect,
            comm,
            dueagent,
            fuel,
            SECURITY,
            others,
            duecarrier,
            freightdue,
            discount,
            carrierrate,
            airline,
            iatarate,
            dgr)
SELECT tableawbdetails.airwaybillnumber,
      tableawbdetails.flightdate,
      tableawbdetails.departurefrom,
      tableawbdetails.destination,
      tableawbdetails.via1,
      tableawbdetails.grossweight,
      tableawbdetails.costingchargeableweight,
      tableawbdetails.prepaid,
      tableawbdetails.totalprepaid,
      tableawbdetails.freightcollect,
      tableawbdetails.comm,
      tableawbdetails.dueagent,
      tableawbdetails.fuel,
      tableawbdetails.SECURITY,
      tableawbdetails.others,
      tableawbdetails.duecarrier,
      tableawbdetails.freightdue,
      tableawbdetails.discount,
      tableawbdetails.carrierrate tableawb.airline,
      tableawbdetails.iatarate,
      tableawbdetails.dgr
FROM  (tableawb
        INNER JOIN tableawbdetails
          ON tableawb.airwaybillnumber = tableawbdetails.airwaybillnumber)
WHERE  tableawb.airline = 'MU'
      AND tableawbdetails.report = 'MU'
      AND ( tableawb.costing <> 0
              OR tableawbdetails.destination = 'CNX' )
      AND tableawbdetails.firsthalf = true
      AND MONTH = 'June'
      AND YEAR = '2011'

---

thx for the help... i solve the error

"Using this tool, I was able to format your sql to a more readable format"

Cool tool :D

Quote:

---

Originally Posted by TysonLPrice

"Using this tool, I was able to format your sql to a more readable format"

Cool tool :D

---

yes, everyone should book mark it. :thumb: