CODEWITHSUNDEEP
mysql
Anti-Dentite
Anti-Dentite

Reputation: 591

mysql query to select many-to-many

I have the following tables:

Widgets table is a simple product/listing table with an id, user_id, added/updated, etc.

Fields table lists field names for widgets. There can be any number of fields (e.g., height, width, price, year, colour, etc.). Fields table:

Each widget can have any number of fields and values associated with it. The Widgets_Fields table will look like so:

If you simply want to get all of the fields for a widget or group of widgets, it is pretty straight forward:

select widget_id from widgets_fields where widget_id = xxx

But what if you are searching for widgets and want to find only those widgets that have certain values for their fields? Also doable

select widget_id from widgets_fields where field_id = xxx and value = xxx

My problem arises when I want to select widgets based upon multiple fields. For example, all widgets that are either 'red' or 'orange' AND also have a year of 2012 or 2011

select widget_id from widgets_fields where 
(field_id = xxx and (value = 'red' or value = 'orange')
and
(field_id = xxy and (value = 2012 or value = 2011)

This produces an impossible where. Alternatively I could do something like this

select widget_id from widgets_fields where
field_id in (xxx, xxy)
and value in ('red', 'orange', 2012, 2011)

but it won't necessarily return what I want as it will be looking for the given values in any of the provided field ids.

So, finally, my question is whether or not there is any way to properly build a query to accomplish this? I'm certain there must be, but I've been working on it for a while and have yet to come up with a solution.

Upvotes: 1

Views: 92

Answers (2)

Alex
Alex

Reputation: 23300

all widgets that are either 'red' or 'orange' 
AND also have a year of 2012 or 2011

can be accomplished via subquerying and INNER JOINing the results together

SELECT widget_id FROM
(SELECT widget_id FROM widgets_fields
WHERE field_id = 'color' and value in ('red', 'orange')) ResultSetA
INNER JOIN
(SELECT widget_id FROM widgets_fields
WHERE field_id = 'year' and value in (2011, 2012)) ResultSetB
ON ResultSetA.widget_id = ResultSetB.widget_id

in plain words, this would be "get the id of all red and orange widgets, then get the id of all widgets from 2011 or 2012, then give me the ids which appear in both lists.

Upvotes: 1

Vilx-
Vilx-

Reputation: 106920

Two ways come instantly to mind. One is to use EXSISTS subqueries:

select * from widgets where
    exists (select * from widgets_fields where field_id = xxx and (value = 'red' or value = 'orange') and widget_id=widgets.id)
    and exists (select * from widgets_fields where field_id = xxy and (value = 2012 or value = 2011) and widget_id=widgets.id)

Another is to use JOINs with subqueries:

select a.widget_id from
    (select widget_id from widgets_fields where field_id = xxx and (value = 'red' or value = 'orange')) a
    join (select widget_id from widgets_fields where field_id = xxy and (value = 2012 or value = 2011)) b
        on (a.widget_id=b.widget_id)

I'm not sure about the performance, and it probably depends on how much data you have and what indexes there are.

Upvotes: 1

Related Questions