SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Jul 2009
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to LEFT JOIN a SubQuery?

    What I'm doing here is to add a column (named outOfStock) to my existing table prod_info.

    I used this query to generate my outOfStock table.

    PHP Code:
    SELECT prod_idXSSSplusMLXL FROM prod_info AS outOfStock WHERE
         XS    IS NULL 
    &&
         
    S     IS NULL &&
         
    Splus IS NULL &&
         
    M     IS NULL &&
         
    L     IS NULL &&
         
    XL    IS NULL 
    Then I used a LEFT JOIN to combine them.

    PHP Code:
    SELECT FROM prod_info LEFT JOIN (
      
    SELECT prod_idXSSSplusMLXL FROM prod_info AS outOfStock WHERE
         XS    IS NULL 
    &&
         
    S     IS NULL &&
         
    Splus IS NULL &&
         
    M     IS NULL &&
         
    L     IS NULL &&
         
    XL    IS NULL
    ON prod_info.prod_id outOfStock.prod_id 
    It prompts me this error message:
    Every derived table must have its own alias.
    I'm totally a goner at subqueries and joins. How does it mean and how do I achieve what I want?

    Oh yes, please recommend some really simple reading on joins and subqueries. Most that I'd seen are quite difficult to understand.

  2. #2
    SitePoint Addict
    Join Date
    Jul 2009
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I'd got it to work with this:
    PHP Code:
    SELECT FROM prod_info LEFT JOIN (
      
    SELECT prod_idXSSSplusMLXL FROM prod_info AS outOfStock WHERE
         XS    IS NULL 
    &&
         
    S     IS NULL &&
         
    Splus IS NULL &&
         
    M     IS NULL &&
         
    L     IS NULL &&
         
    XL    IS NULL
    ) AS outOfStock ON prod_info.prod_id outOfStock.prod_id 
    But its not what I wanted.

    How do I sort of create a new column and add it to my existing table?

    Basically I want to find all products that are out of stock.
    Store them in a column named outOfStock.
    And add it to my exisiting prod_info table.

    outOfStock column will contain the prod_id of those products which have nulls in XS, S, Splus, M, L, XL.

  3. #3
    SitePoint Addict
    Join Date
    Jul 2009
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh yes..i found the answer. Had the subquery wrong in the 1st place.

    PHP Code:
    SELECT FROM prod_info LEFT JOIN (
      
    SELECT prod_id as outOfStock FROM prod_info WHERE
         XS    IS NULL 
    &&
         
    S     IS NULL &&
         
    Splus IS NULL &&
         
    M     IS NULL &&
         
    L     IS NULL &&
         
    XL    IS NULL
    ) AS outOfStock ON prod_info.prod_id outOfStock.outOfStock 

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    SELECT 
        prod_info.*
      , prod_id AS outOfStock 
    FROM prod_info 
    WHERE XS    IS NULL 
    AND   S     IS NULL 
    AND   Splus IS NULL 
    AND   M     IS NULL 
    AND   L     IS NULL 
    AND   XL    IS NULL

  5. #5
    SitePoint Addict
    Join Date
    Jul 2009
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Code MySQL:
    SELECT 
        prod_info.*
      , prod_id AS outOfStock 
    FROM prod_info 
    WHERE XS    IS NULL 
    AND   S     IS NULL 
    AND   Splus IS NULL 
    AND   M     IS NULL 
    AND   L     IS NULL 
    AND   XL    IS NULL
    wow...thats nice. thanks!


Bookmarks

Posting Permissions

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