Thomas’ Developer Blog

January 31, 2012

Filtering many to many queries in mySQL using “having.”

Filed under: sql — sanzon @ 6:21 am

I rarely update this blog anymore except when I run into something that really bugs me. I started with 3 tables.

Product_ID Name
1 Orange
2 Apple
3 Banana
Category_ID Name
1 Red
2 Orange
3 Round
4 Long
Product_ID Category_ID
1 1
1 3
2 2
3 4

 

 

 

 

The problem is I wanted to select only products that were round and red. However, the problem is you can’t use a where statement like:

“select distinct product_id from product join product_to_category using(product_id)  where category_id = 1 and category_id = 3”

This would return 0 results.

The best way to do this is to use a Group By Product_ID along with a Having clause as such:

“select distinct product_id from product join product_to_category using(product_id)  group by product_id having sum(if(category_id in (1,3),1,0)) = 2”

This will return only products that are both red and round.

How does the having clause work?

Having is similar to where except for it takes place after all rows have been collected. Generally it filters the results. Now let’s look at the statement:

“having sum(if(category_id in (1,3),1,0)) = 2”

What this code does is pretty simple. It checks each category_id in product_to_category for a match. If there is a match a 1 is returned, if no matches it returns a 0. Because they are grouped we add up the number of matches. In this case there are 2 categories we are looking for so this value must “= 2”.

That’s it! Fast and clean!

Advertisements

24 Comments »

  1. Thanks for sharing your thoughts about how to configure best buy modem for comcast internet service.
    Regards

    Comment by how to configure best cable wireless modem for comcast internet — July 19, 2014 @ 3:20 am

  2. This website was… how do I say it? Relevant!!
    Finally I have found something which helped me. Many thanks!

    Comment by how to configure best cable best comcast modem for gaming — August 9, 2014 @ 7:30 am

  3. What’s up, for all time i used to check blog posts here in the early hours in the daylight, for the
    reason that i like to learn more and more.

    Comment by how to configure best comcast cable modem for xbox live — August 12, 2014 @ 7:28 pm

  4. Hi would you mind letting me know which web
    host you’re working with? I’ve loaded your blog in 3 different internet browsers and I must say this blog loads a lot quicker then most.

    Can you suggest a good web hosting provider at a reasonable price?
    Thanks a lot, I appreciate it!

    Comment by how to configure best best docsis 3 cable modem for comcast internet — September 2, 2014 @ 10:08 pm

  5. What’s up, yup this post is truly nice and I have learned lot of things from it concerning blogging.
    thanks.

    Comment by how to configure best arris cable modem for comcast — September 4, 2014 @ 5:01 pm

  6. Magnificent items from you, man. I’ve be mindful your stuff
    previous to and you are simply extremely wonderful. I really like what you have got right here,
    certainly like what you are stating and the
    way in which through which you assert it. You make it enjoyable
    and you still take care of to keep it wise. I can’t wait to read far more from you.

    That is actually a terrific web site.

    Comment by how to configure arris wireless cable modem for — September 20, 2014 @ 1:03 am

  7. WOW just what I was searching for. Came here by searching for netgear dgn2200 att

    Comment by dgn2200 fastweb commerciale — September 20, 2014 @ 7:27 am

  8. This is really interesting, You are a very skilled blogger.
    I have joined your feed and look forward to seeking more of your excellent post.
    Also, I have shared your web site in my social networks!

    Comment by how to configure best cable best modem for xbox live — September 20, 2014 @ 9:38 am

  9. Awesome post.

    Comment by how to configure best buying a arris cable modem for comcast — September 20, 2014 @ 9:50 am

  10. My relatives all the time say that I am killing my time here at web,
    but I know I am getting know-how everyday by
    reading thes good posts.

    Comment by how how how to configure modem billion — September 20, 2014 @ 10:35 am

  11. I am now not certain where you are getting your information, but
    good topic. I needs to spend some time learning more or working out more.
    Thank you for wonderful info I used to be searching for
    this info for my mission.

    Comment by how to configure tp link adsl modem for bsnl — September 22, 2014 @ 9:13 pm

  12. Thank you for sharing your info. I really appreciate your efforts and I will be waiting for your further
    write ups thank you once again.

    Comment by how to setup billion modem — September 23, 2014 @ 2:31 am

  13. Awesome things here. I’m very glad to peer your post.

    Thanks so much and I’m taking a look forward to contact you.
    Will you kindly drop me a mail?

    Comment by first daredevil comic for sale — September 25, 2014 @ 5:15 am

  14. Thank you a lot for sharing this with all of us you really
    understand what you’re talking approximately! Bookmarked. Kindly additionally visit my web site =).
    We could have a link exchange contract among us

    Comment by first date tips for women — September 25, 2014 @ 5:23 am

  15. fantastic publish, very informative. I ponder why the other experts of this sector don’t understand this.
    You should proceed your writing. I am sure, you’ve a huge readers’ base already!

    Comment by stingy meaning in urdu — September 25, 2014 @ 9:41 pm

  16. I quite like looking through an article that will make people think.

    Also, thank you for permitting me to comment!

    Comment by how to configure best cable the whats the best modem — September 27, 2014 @ 3:36 pm

  17. Whats up are using WordPress for your blog platform?

    I’m new to the blog world but I’m trying to get started and create
    my own. Do you need any coding knowledge to make your own blog?
    Any help would be really appreciated!

    Comment by how to configure best comcast do you do u need a modem — September 27, 2014 @ 5:05 pm

  18. Good article. I’m facing some of these issues as well..

    Comment by how to configure arris wireless cable modem for — September 28, 2014 @ 7:18 pm

  19. I hardly leave responses, but i did a few searching and wound up here Filtering many to many queries in mySQL
    using having. | Thomas’ Developer Blog. And I do have a
    few questions for you if it’s allright. Could it be simply me or does it seem like a few
    of these comments look as if they are written by brain dead people?
    :-P And, if you are posting on other online social sites, I’d like to keep up with anything
    new you have to post. Would you make a list of every one of your communal sites like your Facebook page, twitter
    feed, or linkedin profile?

    Comment by how to configure arris wireless cable modem for — September 30, 2014 @ 6:12 pm

  20. Hey there just wanted to give you a brief heads up and let
    you know a few of the pictures aren’t loading correctly. I’m not sure why
    but I think its a linking issue. I’ve tried it in two
    different internet browsers and both show the same outcome.

    Comment by how to configure best cable cable modem for xbox 360 — September 30, 2014 @ 9:04 pm

  21. If some one wants to be updated with hottest technologies after that he must
    be visit this site and be up to date every day.

    Comment by how to configure best best comcast how to connect xbox 360 — September 30, 2014 @ 11:05 pm

  22. I like the valuable info you provide in your articles.
    I’ll bookmark your weblog and check again here
    regularly. I am quite sure I will learn many new stuff right here!
    Good luck for the next!

    Comment by Il mondo dell’archeologia — October 3, 2014 @ 2:12 am

  23. Bob Jordan Retiring From TV News

    Robert H. Jordan Jr. is a weekend anchor on WGN-TELEVISION’s Information at
    Nine. He also produces, writes and studies information tales for the weekday and weekend information. A veteran of WGN-TV, Robert first joined the station in March 1973.

    Robert obtained his Ph.D. in Philosophy of Education with a minor
    in Ethics from Loyola University Chicago in Could 1999.
    He graduated with a Master of Arts diploma from Northeastern Illinois College in Could
    1994 with a level in Speech, and his Bachelors diploma in Common Studies from Roosevelt
    College in Chicago.

    Jordan joined WGN-TELEVISION as a common assignment reporter; he also served as an anchor for
    the One ‘clock News. He left WGN-TELEVISION in 1978
    and joined the CBS News Midwest Bureau in Chicago.
    In his two years at CBS, Jordan spent الاردن اليوم time
    masking tales in the Midwest for the CBS Night Information with Walter Cronkite.
    He returned to WGN-TV in 1980.

    Comment by الاردن اليوم — January 30, 2015 @ 12:19 am

  24. Excellent article. Keep posting such kind of info
    on your page. Im really impressed by your site.

    Hi there, You’ve done an incredible job. I will definitely digg it
    and in my opinion suggest to my friends. I am confident they will be benefited from this website.

    Comment by fashion island movie theater — August 28, 2016 @ 1:30 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: