2

This is a followup to my earlier question which got me part of the way to the goal.

Here is what I'm starting with:

SELECT * FROM MyTable WHERE County IN (SELECT value From dbo.fn_Split(@counties, ','))

Here's the scenario: In my table I've got a column named County. Each record can have multiple counties in the County column delimited with commas (I know this is bad form, I didn't do it). For example: county1, county22, county41. Some records may have only one county (say county13) others might have all the counties. So: county1, count2, county3... through county45 (yes, it's terrible, I know).

In the app I'm trying to build users can select multiple counties or even all counties in the same format as above (county1, county2, county3...). Thanks to Martin's help in the previous question I can get it to return records that have each of the counties individually but not the records that might contain all of the counties.

For example: The user selects county4, county26. I need to have the records returned that have just county4 and county26 as well as any that might contain both of them as part of a larger set (like all 45 of them).

Hope this is clear and I didn't make it more convoluted than necessary. Any assistance is very, very, very much appreciated!

To illustrate:

        County
Record1 county1, county14, county26
Record2 county14
Record3 county1, county2, ... through county45

User Submission: county1, county26

Returns:    Record1 and Record3
Community
  • 1
  • 1
user3738691
  • 45
  • 1
  • 1
  • 5
  • Oh, I see. The column itself contains a comma separated list. – Martin Smith Jun 14 '14 at 08:39
  • So, is this impossible or am I not asking correctly/clearly. – user3738691 Jun 17 '14 at 12:16
  • Did this ever get resolved? My thought would be to build a temp table that contains the normalized data from the table with the comma-delimited field, and another temp table from the user submission. – RobertB Nov 06 '15 at 16:57
  • Why can't you upgrade the database schema and data? – David Crook Dec 11 '15 at 17:32
  • you're probably going to need to expand the csv into a separate table using recursion, see this [link](http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) for how. You'll want to put that in a separate table, probably just with your primary keys. At that point, you can do something like this: `SELECT * FROM MyTable WHERE id IN (select id from #tempTable where county in (SELECT value From dbo.fn_Split(@counties, ','))` – David784 Mar 26 '16 at 23:04

1 Answers1

1

Not sure if I understood the question, but here is how I interpret it:
You need to return rows from your table for one or more selected items.
Also, you want to be able to select ALL items at once without passing the whole list.

I'd do that using Stored Procedure with 2 parameters:
@Selection SMALLINT @TVP_County CountyTableType (It is Table Valued Variable. See: https://msdn.microsoft.com/en-us/library/bb510489.aspx)

If @Selection = 1 then you join @TVP_County with your table to get results.
If @Selection = 0 you return ALL records from your table w/o join and do not use @TVP_County at all.
If @Selection = -1 then you exclude @TVP_County items from your table. In that case you will be able to do a reverse marks. User will be able to select just few counties, which he/she does not want to see.

Of cause, within a stored procedure you have to implement the logic to run three different queries depending on the first parameter.

Slava Murygin
  • 1,951
  • 1
  • 10
  • 10