Jason Jason - 2 months ago 10
SQL Question

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.

Answer

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