SQL error in Laravel 6 when joining tables

Question

So i wrote this query in phpmyadmin and it is working correctly

SELECT als.name,als.airlineCode, flights.planeNumber, flights.departureAirport, 
dep_air.name,flights.departurelTime, flights.arrivalAirport, 
arr_air.name,flights.arrivalTime FROM `flights`
JOIN airports AS dep_air
ON flights.departureAirport = dep_air.airportCode
JOIN airports as arr_air
ON flights.arrivalAirport = arr_air.airportCode
JOIN airlines as als
ON flights.airlineReference = als.airlineCode
WHERE dep_air.airportCode = 'YYZ' AND arr_air.airportCode = 'YUL' ;

here it is in action

however when i try this in laravel in the controller

public function formSubmit(){


       $flights = DB::table('flights')
        ->select('als.name','als.airlineCode','flights.planeNumber','flights.departureAirport','dep_air.name','flights.departurelTime','flights.arrivalAirport','arr_air.name','flights.arrivalTime')
        ->join('airports as dep_air','flights.departureAirport','dep_air.airportCode')
        ->join('airports as arr_air' ,'flights.arrivalAirport','arr_air.airportCode')
        ->join('airlines as als,','flights.airlineReference','als.airlineCode')
        ->where('dep_air.airportCode','=', 'YYZ')
        ->where('arr_air.airportCode','=', 'YUL')
        ->get();
        dd($flights);
        return view('oneWayTrips', compact('flights'));
    }

I get the following error

Illuminate\Database\QueryException SQLSTATE[42S22]: Column not found: 1054 Unknown column 'als.name' in 'field list' (SQL: select als.name, als.airlineCode, flights.planeNumber, flights.departureAirport, dep_air.name, flights.departurelTime, flights.arrivalAirport, arr_air.name, flights.arrivalTime from flights inner join airports as dep_air on flights.departureAirport = dep_air.airportCode inner join airports as arr_air on flights.arrivalAirport = arr_air.airportCode inner join airlines as als, on flights.airlineReference = als.airlineCode where dep_air.airportCode = YYZ and arr_air.airportCode = YUL)

UPDATE:

The query is almost working after the typo fixes but i've noticed one of the values is missing the arr_air.name . the dep_air.name is working fine and showing the departure airport name, arr_air.name represents the name for the arrival airport

this is the query at the moment

oops thanks for letting me know everyone, however i got a new error that i updated in the original post. — Zaki Kazmi
updated after fixing typo's is it because I have a field called name in all 3 tables so they are overwriting each other because the als.name should be air canada, the other two should be airport names — Zaki Kazmi

Shantanu Sharma Shantanu Sharma · Accepted Answer · 2019-11-25T05:31:41

1

votes

I think you have to check "airlines" table have "name" field ....

SQL error in Laravel 6 when joining tables

1 Answers