0

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."

Community
  • 1
  • 1
justiceorjustus
  • 2,017
  • 1
  • 19
  • 42
  • What's on your `Products` table? You are doing a `Distinct` on the `Products` table only, so you won't see the `Countries` in your results list. So how do you know your query is wrong? – SS_DBA Nov 28 '16 at 20:42
  • I've added the counties name and have seen it is wrong. I just have that there to show I'd like 1 row for each product, not one for each product/county combination. – justiceorjustus Nov 28 '16 at 20:44
  • `JOIN` the `WantedCountries` to the `Countries` table on `CountryName`. And remove the Where clause. – SS_DBA Nov 28 '16 at 20:45
  • Since I'm using a split string function, I'm getting an error for ``JOIN ( SELECT Item FROM SplitStrings_CLR(@Counties, N',') ) clr ON clr.Item = c.CountyName`` from my test variable ``SET @Counties = N'Stark,Summit';`` What I'm saying above is WantedCounties resembles the result of the function, logically. It's not letting me join on the Item split from the list. :( EDIT: Scratch what I just said. I put it in the wrong spot overall. Let me try. – justiceorjustus Nov 28 '16 at 20:50
  • Okay @WEI_DBA, I gave it a shot and I'm still getting results where the product exists in one but not the other. I think it has to do with my Products table having to be joined on ProductsCounties and "WantedCounties" in order to work... so it's still grabbing all of the County matches from ProductsCounties. – justiceorjustus Nov 28 '16 at 21:01
  • I guess I'm confused on what you want. Can you provide table data and expected results? – SS_DBA Nov 28 '16 at 21:51

1 Answers1

0

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."

Community
  • 1
  • 1
justiceorjustus
  • 2,017
  • 1
  • 19
  • 42