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.

the question is not clear and also Title and it's content are unmatched. — Salil
– Salil, Commented Apr 4, 2010 at 12:38

John Cargo · Accepted Answer · 2015-07-20 07:55:11Z

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' ;

edited Jul 20, 2015 at 7:55

John Cargo

2,1913 gold badges32 silver badges66 bronze badges

answered Apr 4, 2010 at 12:51

Salil

47.7k22 gold badges127 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Over a year ago

@Salil Thanks! That's exactly what I need. Cheers. SELECT DISTINCT ( 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'

Sebastien Lorber · Accepted Answer · 2010-04-04 13:37:32Z

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...

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related