Search code examples
sqlsql-server-2012many-to-many

SQL statement for many-to-many WHERE Item IN ALL results, not just one

Put into a simplified way, I have three tables: Products, ProductsCounties, and Counties. We can only sell certain products in certain counties, so there is a many-to-many relationship between Products and Counties with ProductsCounties.

To make it even simpler to ask (because I'm using split strings from a comma separated list in a stored procedure... among magic), let's say I just have a table called WantedCounties with CountyName's County1, County2, and County3 as records. This is basically the result of the split string function based on what I give it. My problem is, I want to give three counties (think WantedCounties is going to be what I'm giving) and that Product has to be available in ALL THREE and not just one, hence why my IN statement fails here.

SELECT DISTINCT p.* FROM Products p
JOIN ProductsCounties pc ON pc.ProductId = p.ProductId
JOIN Counties c ON pc.CountyId = c.CountyId
WHERE c.CountyName IN (SELECT CountyName FROM WantedCounties)

This is as much as I can narrow down my problem for sake of making it easy to ask. Does anyone know how to do this? I want only products that have a relationship with every single county given, not just one match IN the subquery.

I think my issue is I can't grasp how to deal with there being multiple rows being selected because of each county match on the join.

I have also tried something along the lines of = ALL (SELECT CountyName FROM WantedCounties) to no avail.

Edit: I found the solution here. I had to include this:

GROUP BY --All my refrences
HAVING COUNT(DISTINCT c.CountyName) = (
SELECT CountyName
FROM WantedCounties)
)

This only returns products that have a match in every County in WantedCounties. So there had to be all 3 counties in my results to match the 3 counties in WantedCounties in order for it to say "that's all of them."

Solution

  • I found the solution here. I had to include this:

    GROUP BY --All my refrences
HAVING COUNT(DISTINCT c.CountyName) = (
SELECT CountyName
FROM WantedCounties)
)

    This only returns products that have a match in every County in WantedCounties. So there had to be all 3 counties in my results to match the 3 counties in WantedCounties in order for it to say "that's all of them."