5

I have a table products and a table locations which are linked together in a many-to-many relationship with a table products_locations. Now a client can select a set of products, and I want to run a query that selects only the locations, where ALL of the selected products are available.

This seemed pretty straight forward at first, but I see myself being quite baffled by how to achieve this. I initially thought I could get all the correct location-ids with something like

SELECT location_id
FROM products_locations
WHERE product_id = ALL [the user selected product ids]

But on second thought that does not appear to make sense either (the structure of products_locations is quite simply [product_id, location_id].

Any suggestion on how to structure such a query would be appreciated. I feel like I am overlooking something basic..

EDIT: I am using mysql syntax/dialect

Quick sample: Given the following tables

| products   | | locations | | products_locations       |
| id | name  | | id | name | | product_id | location_id |
|------------| |-----------| |--------------------------|
| 1  | prod1 | | 1  | locA | | 1          | 2           |
| 2  | prod2 | | 2  | locB | | 2          | 1           |
| 3  | prod3 | |-----------| | 2          | 2           |
|------------|               | 3          | 1           |
                             |--------------------------|

If a user selects products 1 and 2, the query should return only location 2. If the user selects products 2 and 3, the query should return location 1. For 1, 2, and 3, no location would be valid, and for product 2, both locations would be valid.

Kugelblitz
  • 582
  • 5
  • 24
  • Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... –  Sep 26 '18 at 09:51
  • Add some sample table data and the expected result. Take a look at https://stackoverflow.com/help/mcve before you start. – jarlh Sep 26 '18 at 09:53
  • I'm using mysql as a dialect, I added it to the original question. My bad, I forgot to specify – Kugelblitz Sep 26 '18 at 09:53
  • @jarlh you are right, I mistakenly omitted an example out of fear of confusing the reader, I added one now since I was able to boil it down. Thanks! – Kugelblitz Sep 26 '18 at 10:13

1 Answers1

3

I figured out a query that achieves what I need. Though it is not as clean as I had hoped, it seems to be a robust approach to what I'm trying to query:

SELECT t.location_id
FROM (SELECT location_id, COUNT(*) as n_hits
      FROM products_locations
      WHERE product_id IN [the user selected products]
      GROUP BY location_id) t
WHERE n_hits = [the number of user selected products];

Explanation:

  1. I create a temporary table t which contains every location_id that has at least one matching product in the user's selection, together with the number of times that location matches a product in the user's selection. This is achieved by grouping the query by location_id.
  2. I select the location_id(s) from that temporary table t, where the number of hits is equal to the number of products the user had selected. If that number is lower, I know that at least one product did not match that location.
Kugelblitz
  • 582
  • 5
  • 24
  • Found this solution for the same problem in my own way.. And after that, found yours. Maybe its the only solution for this or we're thinking equal. – Gargamil Mar 31 '20 at 21:37