SELECT query help

[appy]

Hi All,

I've been asked by a friend to help with some design for a site built a couple of years ago and I've come across a couple of data errors that MySQL should be able to sort out but I'm afraid that my MySQL knowledge is pretty limited.

The situation is a product search and uses the query below...

SELECT products_description.products_id, products_description.language_id, products_to_categories.categories_id
FROM products_description
INNER  JOIN products_to_categories ON products_description.products_id = products_to_categories.products_id
WHERE products_description.products_name LIKE  '%searchterm%'
OR products_description.products_description LIKE  '%searchterm%'

However, products are being returned that are not supposed to be 'active'.

There's a table called

product

which has a column called

products_status

which can be a 1 or 0.

So what I need to do is add to the original query

AND product.products_status = 1

but can't quite work out the way to do it.

Any help would be great.

Thanks,

Pete

[c2uk]

as shown in another thread, my SQL skills are also limited, but it looks to me like we need another JOIN between the products table and the products_description table, likely again on the products_id (I assume this field is available in the products table?). Then

WHERE product_status = 1 AND (products_description.products_name LIKE  '%searchterm%'
OR products_description.products_description LIKE  '%searchterm%')

Let's see what others are saying

[swampBoogie]

select products_description.products_id, 
       products_description.language_id, 
       products_to_categories.categories_id
  from products_description
  join products_to_categories 
    on products_description.products_id = products_to_categories.products_id
  join products 
    on products.products_id = products_description.products_id
   and products.products_status = 1
 where products_description.products_name like '%searchterm%'
    or products_description.products_description like '%searchterm%'

You did not mention how to join the products table, so you may have to change this line

 on products.products_id = products_description.products_id

depending on your table definitions.

[PhilipToop]

To do what you want to do it must be possible to join the product table to one of the other two tables in the Select statement. In the same way as the products_to_categories table is joined to the products_description. That requires that you identify the field that the two tables have in common.

I would speculate that your table product has a field id and that this is the same as the products_id field on the products_description table.

so you need a

LEFT JOIN product ON product.id = products_description.products_id

and then you can extend you WHERE clause

WHERE (products_description.products_name LIKE  '%searchterm%'
OR products_description.products_description LIKE  '%searchterm%')
AND product.products_status = 1

[appy]

Hey thanks a lot for the quick responses. An extra join and where clause has done the trick perfectly.

Pete

[r937]

philip, your LEFT JOIN should be an INNER JOIN

it is extremely unlikely that there will be a row in products_description with a product id that isn't in the products table