Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Posts
    7

    Unanswered: Why Doesn't this LEFT OUTER JOIN query work

    Hello!

    I have 2 tables foods & order_transac...all I want do is display
    the Food_ID from foods table even though there is no Qty value
    for it in order_transac.All I am trying to do in my app
    is an order will be placed...by a user...later they log in again
    & want to edit their order...so when they click on Update Order Button
    I am taking these existing values to a edit page through a query. So, the user has placed an order only for Reg Milk & Skim Milk
    first time now he wants to add Low Fat Chocolate Milk tooo...
    so in my edit page all these prev values...appear & Now I want to grab Low Fat Chocolate Milk Food_ID too with a vlue zero in it...so that user can enter a new value...


    SELECT order_transac.Qty, foods.Category, foods.Food_Name, foods.Unit FROM foods LEFT OUTER JOIN ON order_transac foods.FOOD_ID = order_transac.Food_ID where order_transac.Order_ID = 101;

    Any Immediate Help is higly appreciated...

    CREATE TABLE foods (
    Food_ID varchar(5) NOT NULL default '',
    Category varchar(5) NOT NULL default '',
    Food_Name varchar(25) NOT NULL default '',
    Unit varchar(10) NOT NULL default '',
    PRIMARY KEY (Food_ID),
    FULLTEXT KEY Food_ID (Food_ID)
    ) TYPE=MyISAM;

    #
    # Dumping data for table `foods`
    #

    INSERT INTO foods VALUES ('RM', 'Dairy', 'Regular Milk', 'gallons');
    INSERT INTO foods VALUES ('SM', 'Dairy', 'Skim Milk', 'gallons');
    INSERT INTO foods VALUES ('LCM', 'Dairy', 'Low Fat Chocolate Milk', 'gallons');
    INSERT INTO foods VALUES ('WWB', 'Bread', 'Whole Wheat Bread', 'Loaf');


    # Table structure for table `order_transac`
    #

    CREATE TABLE order_transac (
    Order_ID int(5) NOT NULL auto_increment,
    Food_ID varchar(5) NOT NULL default '',
    Qty tinyint(4) NOT NULL default '0',
    PRIMARY KEY (Order_ID,Food_ID)
    ) TYPE=MyISAM;

    #
    # Dumping data for table `order_transac`
    #

    INSERT INTO order_transac VALUES (101, 'RM', 3);
    INSERT INTO order_transac VALUES (101, 'SM', 4);
    INSERT INTO order_transac VALUES (101, 'WWB', 2);


    Any Immediate Help is highly appreciated...

    Thanks,
    Sean

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your ON keyword was in the wrong place and the 101 condition belongs in the ON clause, not the WHERE clause:
    Code:
    select order_transac.Qty
         , foods.Category
         , foods.Food_Name
         , foods.Unit 
      from foods 
    left outer 
      join order_transac 
        on foods.FOOD_ID 
         = order_transac.Food_ID 
       and order_transac.Order_ID = 101
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Posts
    7
    Rudy,

    As Always Thanks! A Million! your query works...

    Have a Nice Week Ahead!

    Thanks,
    Sean

Posting Permissions

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