Using Inner join and Outer Join at a time

sashi34u · #1 (**permalink**) 06-16-09, 16:24

Hi,

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.

Thanks,
Sashi

Code:

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

r937 · #2 (**permalink**) 06-16-09, 17:43

the query makes very little sense the way it is written

please do a SHOW CREATE TABLE for each table involved in the query

sashi34u · #3 (**permalink**) 06-16-09, 18:43

Hi,

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

Code:

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.

Thanks,
Sashi

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.

mysql> use vt_vpm;
Database changed

mysql> show create table buildings;
+-----------+-------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------+
| Table | Create Table

|
+-----------+-------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------+
| buildings | CREATE TABLE `buildings` (
`BuildingName` varchar(60) default NULL,
`BuildingCode` varchar(5) NOT NULL,
PRIMARY KEY (`BuildingCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+-------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------+
1 row in set (0.00 sec)

mysql> show create table parkinglots;
+-------------+-----------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------------------+
| Table | Create Table

|
+-------------+-----------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------------------+
| parkinglots | CREATE TABLE `parkinglots` (
`PARKING_LOT_NAME` varchar(50) NOT NULL,
`PARKING_LOT_CODE` varchar(10) NOT NULL,
`FS` int(4) default NULL,
`TA` int(4) default NULL,
`COM` int(4) default NULL,
`RES` int(4) default NULL,
`FS_COM` int(4) default NULL,
`Visitor` int(4) default NULL,
`CP` int(4) default NULL,
`MC` int(4) default NULL,
`TIMED` int(4) default NULL,
`GEN` int(4) default NULL,
`SV` int(4) default NULL,
`HC` int(4) default NULL,
`METER` int(4) default NULL,
`TOTAL` int(4) default NULL,
PRIMARY KEY (`PARKING_LOT_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+-----------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table buildings_lots;
+----------------+--------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------+
| Table | Create Table

|
+----------------+--------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------+
| buildings_lots | CREATE TABLE `buildings_lots` (
`Building_Code` varchar(10) NOT NULL,
`Parking_Lot_Code` varchar(10) NOT NULL,
`Distance` varchar(5) NOT NULL,
PRIMARY KEY (`Building_Code`,`Parking_Lot_Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------------+--------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------+
1 row in set (0.00 sec)

mysql> show create table occupancy;
+-----------+-------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------+
| Table | Create Table

|
+-----------+-------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------+
| occupancy | CREATE TABLE `occupancy` (
`PARKING_LOT` varchar(10) NOT NULL,
`MONTH` varchar(9) NOT NULL,
`DAY` varchar(9) NOT NULL,
`TIME` varchar(5) NOT NULL,
`EMPTY_SPACES` int(3) NOT NULL,
PRIMARY KEY (`PARKING_LOT`,`MONTH`,`DAY`,`TIME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+-------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table image;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------+
| Table | Create Table

|
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------+
| image | CREATE TABLE `image` (
`bldcode` varchar(5) NOT NULL default '',
`Location` varchar(100) default NULL,
PRIMARY KEY (`bldcode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------+
1 row in set (0.00 sec)

mysql>

r937 · #4 (**permalink**) 06-16-09, 18:56

Code:

SELECT buildings.buildingname
     , parkinglots.parking_lot_name
     , parkinglots.FS + 
       parkinglots.COM + 
       parkinglots.RES + 
       parkinglots.FS_COM + 
       parkinglots.VISITOR + 
       parkinglots.GEN            AS total_spaces
     , parkinglots.TIMED +
       parkinglots.METER          AS Metered
     , occupancy.empty_spaces
     , buildings_lots.distance
     , round(buildings_lots.distance/(3*60),1) AS low_time
     , round(buildings_lots.distance/(4*60),1) AS high_time
     , image.Location 
  FROM buildings
INNER
  JOIN buildings_lots
    ON buildings_lots.building_code = buildings.buildingcode
INNER
  JOIN parkinglots
    ON parkinglots.parking_lot_code = buildings_lots.parking_lot_code
INNER
  JOIN occupancy
    ON occupancy.parking_lot = parkinglots.parking_lot_code
   AND occupancy.month = '$Month' 
   AND occupancy.day = '$Today' 
   AND occupancy.Time = '$times'
LEFT OUTER
  JOIN image 
    ON image.BldCode = buildings.BuildingCode
 WHERE buildings.buildingname = '$build' 
ORDER 
    BY occupancy.empty_spaces DESC

sashi34u · #5 (**permalink**) 06-16-09, 19:44

Hi,

Thank you very much. it is working absolutely fine now. Thanks a lot.

-Sashi

Internet Services

Web Development

Digital Marketing

Consumer Tech