Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    1

    Unanswered: sql query problem

    Hey, I am trying to get all the rows from one table as long as they are not in another. I am also using mySQL.

    Here is the sql I need:
    Code:
    select sp.products_id, p.products_id, p.products_ordered, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image
    from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " sp
    where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . $languages_id. "' and p.products_ordered > 0 group by pd.products_id 
    order by rand() 
    DESC limit 20
    but I have to make sure the results are not part of:
    Code:
    select products_id from specials.
    Thanks for any help

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not sure why, but you already appear to have a specials table in the query

    also, i can't really see why you're grouping, either

    try this --
    Code:
    select p.products_id
         , p.products_ordered
         , pd.products_name
         , p.products_price
         , p.products_tax_class_id
         , p.products_image
      from " . TABLE_PRODUCTS . " p
    inner
      join " . TABLE_PRODUCTS_DESCRIPTION . " pd
        on p.products_id 
         = pd.products_id 
       and pd.language_id = '" . $languages_id. "' 
    left outer
      join " . TABLE_SPECIALS . " sp
        on p.products_id 
         = sp.products_id 
     where p.products_status = '1' 
       and p.products_ordered > 0 
       and sp.products_id is null
    order 
        by rand() desc 
    limit 20
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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