How can I write a MySQL query to check multiple rows?

Question

I have a MySQL table containing data on product features:

feature_id    feature_product_id    feature_finder_id    feature_text    feature_status_yn
1             1                     1                    Webcam          y
2             1                     1                    Speakers        y
3             1                     1                    Bluray          n

I want to write a MySQL query that allows me to search for all products that have a 'y' feature_status_yn value for a given feature_product_id and return the feature_product_id. The aim is to use this as a search tool to allow me to filter results to product IDs only matching the requested feature set.

A query of SELECT feature_id FROM product_features WHERE feature_finder_id = '1' AND feature_status_yn = 'y' will return all of the features of a given product. But how can I select all products (feature_product_id) that have a 'y' value when they are on separate lines?

Multiple queries might be one way to do it, but I'm wondering whether there's a more elegant solution based purely in SQL.

Answer 1

@matt

`SELECT feature_product_id FROM cms_finder_product_features WHERE feature_finder_id = '1' AND (feature_id = '1' AND feature_status_yn = 'y') AND (feature_id = '2' AND feature_status_yn = 'y') AND (feature_id = '3' AND feature_status_yn = 'y') ;'

Abovequery never return anything OR Always return Zero rows . You are get confused between AND & OR.

The query should be as follows

SELECT feature_product_id FROM cms_finder_product_features WHERE feature_finder_id = '1' AND ( (feature_id = '1' AND feature_status_yn = 'y') OR (feature_id = '2' AND feature_status_yn = 'y') OR (feature_id = '3' AND feature_status_yn = 'y') ) ;

In simpler way you can use

SELECT feature_product_id FROM cms_finder_product_features WHERE feature_finder_id = '1' AND feature_id in ('1', '2', '3') AND feature_status_yn = 'y' ;

Answer 2

i think Matt is right but it's a bit hard to understand what you really want?

If you want to retrieve a list of products that has a given status_yn set to yes, if there's a product table (with primary key product_id):

SELECT DISTINCT p.product_id FROM products p LEFT JOIN features f ON f.feature_product_id = p.product_id WHERE f.feature_status_yn = 'y' AND f.feature_product_id = '[ARG]'

Matt request should be faster i think anyway since you filter features before joining...

But if you'r new to sql give a try my request and try first the 1st part: SELECT DISTINCT p.product_id FROM products p LEFT JOIN features f ON f.feature_product_id = p.product_id

So that you'll see how your tables are joined and how you can exploit this join...