Unanswered: Using Inner join and Outer Join at a time
I have been trying to use an inner join and outer join command at a time in a query. But, It is not working properly. I believe that my syntax is wrong. Can anyone please help me in this matter. I am posting the query below. Actually this query is being used in PHP. So please ignore the variables. To explain a little bit, the table IMAGE does not have entries to all the building codes in the table Buildings. That is the reason I wanted to use an outer join.
SELECT Location FROM Image LEFT JOIN Buildings ON Image.bldcode=Buildings.BuildingCode buildingname, parking_lot_name, (FS+COM+RES+FS_COM+VISITOR+GEN) AS total_spaces, (TIMED+METER) AS Metered, empty_spaces, distance, round(distance/($low_speed*60),1) AS low_time, round(distance/($high_speed*60),1) AS high_time FROM buildings, buildings_lots, parkinglots, occupancy where ( buildings.buildingcode=buildings_lots.building_code AND parkinglots.parking_lot_code=buildings_lots.parking_lot_code AND parkinglots.parking_lot_code=occupancy.parking_lot) AND buildingname='$build' AND month='$Month' AND day='$Today' AND Time='$times' ORDER BY Distance
Sorry about the ambiguity. I have actually created 5 tables namely buildings, parkinglots, buildings_lots, occupancy and image. The table buildings has 2 columns namely BuildingName and BuildingCode (PRI). The table Parkinglots has about 17 columns and the two main columns are Parking_Lot_Name and Parking_Lot_Code (PRI). The rst of the columns have some numbers stored in them. The third table is buildings_lots and it has 3 columns namely Building_Code (PRI), Parking_Lot_Code (PRI) and Distance. The fourth table is named occupancy and it has 5 columns namely parking_lot (PRI), month (PRI), day (PRI), TIME (PRI) and Empty_spaces. The fifth table is named Image and has 2 coulmns namely bldcode (PRI) and Location. I have provided the results from mysql console for each show create table.
The problem is that, I havent used similar column names for columns containing identical data. I have used different column names in each table.
My original query is
SELECT buildingname, parking_lot_name, (FS+COM+RES+FS_COM+VISITOR+GEN) AS total_spaces, (TIMED+METER) AS Metered, empty_spaces, distance, round(distance/(3*60),1) AS low_time, round(distance/(4*60),1) AS high_time, Location FROM buildings, buildings_lots, parkinglots, occupancy, Image where (buildings.buildingcode=buildings_lots.building_code AND parkinglots.parking_lot_code=buildings_lots.parking_lot_code AND parkinglots.parking_lot_code=occupancy.parking_lot AND Buildings.BuildingCode=Image.BldCode) AND buildingname='$build' AND month='$Month' AND day='$Today' AND Time='$times' ORDER BY empty_spaces DESC
But, the problem is, there are no entries for the column LOCATION of the table IMAGE for many buildings and as such nothing is returned to the web browser when such buildings are chosen. So I was just looking for a way to just outer join the table IMAGE so that data will be displayed even if the table IMAGE has nothing stored in it. Can you please help me with that.
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.51b-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.