sql query problem

ryanf · #1 (**permalink**) 05-03-04, 14:29

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

r937 · #2 (**permalink**) 05-03-04, 21:58

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

Internet Services

Web Development

Digital Marketing

Consumer Tech