SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
Thread: SELECT query help
-
Dec 14, 2009, 05:56 #1
SELECT query help
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...
Code: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%'
There's a table calledCode:product
Code:products_status
So what I need to do is add to the original queryCode:AND product.products_status = 1
Any help would be great.
Thanks,
Pete
-
Dec 14, 2009, 06:14 #2
- Join Date
- May 2005
- Location
- Cardiff
- Posts
- 1,832
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
Code:WHERE product_status = 1 AND (products_description.products_name LIKE '%searchterm%' OR products_description.products_description LIKE '%searchterm%')
Dan G
Marketing Strategist & Consultant
-
Dec 14, 2009, 06:14 #3
- Join Date
- Jan 2004
- Location
- Uppsala, sverige
- Posts
- 700
- Mentioned
- 2 Post(s)
- Tagged
- 1 Thread(s)
Code: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%'
Code:on products.products_id = products_description.products_id
-
Dec 14, 2009, 06:15 #4
- Join Date
- Jun 2007
- Location
- North Yorkshire, UK
- Posts
- 483
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
Code:LEFT JOIN product ON product.id = products_description.products_id
Code:WHERE (products_description.products_name LIKE '%searchterm%' OR products_description.products_description LIKE '%searchterm%') AND product.products_status = 1
-
Dec 14, 2009, 06:58 #5
Hey thanks a lot for the quick responses. An extra join and where clause has done the trick perfectly.
Pete
-
Dec 14, 2009, 08:01 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
Bookmarks