MySQL filter query regarding multiple filters

Question

I'll try and elaborate my question in the simplest form possible. I'm creating an e-commerce website and I'm trying to set up filters for the search result. Everything has been just fine, until I ran into this following issue. When I try to group by id I lose all the other foreign keys to the specification value and if I remove the group by I get a ton of results, each containing own foreign key to the value. (I'm inner joining each article with respective specification values). My question therefore is how do I filter all of these specification ids?

My query (translated and simplified) is as follows:

select * from article
left join article_specification on article_specification.fk_articles=article.id 
where (fk_specification_value=172 or fk_specification_value=175 or fk_specification_value=184) 
group by id order by date desc

By running this query I get 1 result (hence the group by), however if I don't group I can't really do anything with that result set. If I change the ORS into ANDS in the query, I get nothing, since there is only 1 value. That's the root of my question. Thanks in advance, sorry if my question was a little bit poorly sentenced.

Please post a sqlfiddle, with the output you expect for the query. — user180100
– user180100, Commented Feb 26, 2017 at 19:34
You are using group by without aggregation function and the use of select * with group by don't have much sense .. please explain the real result you need for you select and over all why you use group by .. eventually add a proper data sample and the expected result — ScaisEdge
– ScaisEdge, Commented Feb 26, 2017 at 19:35

Gordon Linoff · Accepted Answer · 2017-02-26 19:38:06Z

2

If you want articles that meets all specifications, you can do:

select a.*
from article a join
     article_specification ars
     on ars.fk_articles = a.id 
where ars.fk_specification_value in (172, 175, 184) 
group by a.id
having count(distinct ars.fk_specification_value) = 3
order by a.date desc;

Note: In general, I discourage the use select * with group by. In this case it is okay because a.id is (presumably) the primary key in article. In fact, this use of group by is supported by the ANSI standard. However, interpreting the language in the standard requires understanding what a "functional dependency" is.

If you want articles that meet any of the specifications, you could use the above query without the having clause. However, an exists is more appropriate:

select a.*
from article a 
where exists (select 1
              from article_specification ars
              where ars.fk_articles = a.id  and
                    ars.fk_specification_value in (172, 175, 184) 
             );

answered Feb 26, 2017 at 19:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

alps Over a year ago

This is working exactly as I wanted, thank you so much.

Collectives™ on Stack Overflow

MySQL filter query regarding multiple filters

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related