CODEWITHSUNDEEP
sqlsql-serverpattern-matching
Jason
Jason

Reputation: 53

SQL Query to match a subset of data

I have a table that looks like:

CLASS   VALUE
1       A
1       B
1       C
2       A
2       C
3       B
3       D
4       A
5       C
5       A

I have a user-submitted data-set of values that I want to find any classes whose values are a subset of the user-submitted data-set.

For example,

If data-set was A, B, and C then the results would be class 1, 2, 4, and 5.

If data-set was A and C the results would be class 2, 4, and 5.

If data-set was A, then result would be class 4.

The platform I am on is SQL Server, but really any SQL-based answer would be best.

Upvotes: 0

Views: 3325

Answers (2)

mbadeveloper
mbadeveloper

Reputation: 1270

You can create a stored procedure an pass the user entry as it as string for ex. A,B,C 

Create Procedure dbo.GetClasses
@v_UserEntry Varchar(200)
As
Begin
    Declare @SQLQuery AS NVarchar(1000)
    Declare @ParamDefinition AS NVarchar(300)

    SET @v_UserEntry= REPLACE(@v_UserEntry,',',''',N''')
    Set @SQLQuery ='Select Class'
    Set @SQLQuery = @SQLQuery + ' From TableName'
    Set @SQLQuery = @SQLQuery + ' Where Value in (N'''+@v_UserEntry+''')'
    Set @SQLQuery = @SQLQuery + ' Group By Class'    
    Execute sp_executesql @SQLQuery
End
Go

Upvotes: 0

Siyual
Siyual

Reputation: 16917

As per the comment It's passed as a table. - assuming the table is the variable @UserInput with a single column of Value, you can use a WHERE EXISTS clause to check for the existence of that value in the user-input fields, and pull the DISTINCT Class values.

Select  Distinct Class
From    YourTable   T
Where Exists
(
    Select  *
    From    @UserInput  U
    Where   T.Value = U.Value
)

Your SQL syntax will vary, but this should point you in the right direction, syntactically.

A full example of how to implement this would be as follows:

Creating the User-defined Table Type

Create Type dbo.UserInput As Table
(
    Value   Varchar (10)
)
Go

Creating the Stored Procedure

Create Proc dbo.spGetClassesByUserInput 
(
    @UserInput dbo.UserInput ReadOnly
)
As Begin
    Select  Distinct Class
    From    YourTable   T
    Where Exists
    (
        Select  *
        From    @UserInput  U
        Where   T.Value = U.Value
    )
End
Go

Calling the Stored Procedure with user input

Declare @Input dbo.UserInput
Insert  @Input
Values  ('A'), ('B'), ('C')

Execute dbo.spGetClassesByUserInput @Input

Upvotes: 1

Related Questions