Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2009
    Posts
    12

    Unanswered: Using Inner join and Outer Join at a time

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the query makes very little sense the way it is written

    please do a SHOW CREATE TABLE for each table involved in the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Posts
    12
    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>
    Last edited by sashi34u; 06-16-09 at 19:47.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2009
    Posts
    12
    Hi,

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

    -Sashi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •