SQL that can pulls records by a possible three values or none at all

Question

Say you have WIDGETS table:

• WidgetID int
• ColorID int (Lookup values: Red, Blue, Green, Yellow, Black, Brown)
• SizeID int (Lookup values: Small, Med, Big, Large)
• Weight int (Lookup values: UltraLight, Light, Normal, Heavy, UltraHeavy

Ok that's the general idea of the table. I don't need the lookup names, what I do have is the lookupvalue ID's. So from this table, I would to be able pull lists back that meet say the following crieria:

1. Show me all widgets who are Red, or Blue, or Black
2. Show me all widgets who are Red or Blue, and Small or Med
3. Show me all widgets who are Heavy only
4. Show me all widgets who are heavy and Yellow
5. Show me all widgets who are heavy and yellow and small or big
6. Show me all widgets who are Large only
7. Show me all widgets who are Green only

Get the idea? I've been trying to work on a Stored Proc that allowed me to send in some type of parameters. Even tried dynamic sql but getting weird errors. can't remember now.

My Attempt #3, sort of works if I could get it working in a Proc and be able to condition out which Join's I want

Examples of what I've tried:

Note: In my examples: (CourseID is WidgetID) and (StateID is ColorID) and (CreditTypeID is SizeID) and (SubjectID is WeightID)

Attempt Method 1

ALTER PROCEDURE [dbo].[CourseListFullInfoByStateCreditSubject]
  @StateIDs VARCHAR(200) = '',
  @CreditTypeIDs VARCHAR(200) = '',
  @SubjectTypeIDs VARCHAR(200) = ''
AS

BEGIN

  DECLARE @SQL AS NVARCHAR(MAX)
  SET @SQL = 'SELECT DISTINCT
                     C.CourseID,
                     LU.FirstName,
                     LU.LastName,
                     (SELECT COUNT(ReviewID) FROM Review AS R WHERE R.CourseID = C.CourseID) AS ReviewCount
                FROM [Course] AS C WITH(NOLOCK)
                JOIN LexUser AS LU ON LU.LexUserID = C.PresenterID '

If @StateIDs IS NOT NULL AND @StateIDs <> '''' AND @StateIDs <> '0'
BEGIN
  SET @SQL = @SQL + ' JOIN CourseToState AS CS ON CS.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CS.StateID AS VARCHAR) + '','', '','' + @StateIDs + '','') > 0 '
END

If @CreditTypeIDs IS NOT NULL AND @CreditTypeIDs <> '' AND @CreditTypeIDs <> '0'
BEGIN
  SET @SQL = @SQL + 'JOIN CourseToCreditType As CC ON CC.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CC.CreditTypeID AS VARCHAR) + '','', '','' + @CreditTypeIDs + '','') > 0 '
END

If @SubjectTypeIDs IS NOT NULL AND @SubjectTypeIDs <> '' AND @SubjectTypeIDs <> '0'
BEGIN
  SET @SQL = @SQL + 'JOIN CourseToSubject As CSu ON CSu.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CSu.SubjectID AS VARCHAR) + '','', '','' + @SubjectTypeIDs + '','') > 0 '
END

EXEC sp_executesql @SQL

With Attempt 1 I try and send in the ID's:

[CourseListFullInfoByStateCreditSubject] ''1,2,4'', ''0'', ''0''

...but I get an error, "Incorrect syntax near '1'."

Attempt Method 2, gives same error

DECLARE @SQL AS NVARCHAR(MAX)
SET @SQL = 'SELECT DISTINCT
                   C.CourseID,
                   LU.FirstName,
                   LU.LastName,
                   (SELECT COUNT(ReviewID) 
                     FROM Review AS R 
                    WHERE R.CourseID = C.CourseID) AS ReviewCount
             FROM [Course] AS C WITH(NOLOCK)
             JOIN LexUser AS LU ON LU.LexUserID = C.PresenterID '

If @StateIDs IS NOT NULL AND @StateIDs <> '''' AND @StateIDs <> '0'
BEGIN
  SET @SQL = @SQL + ' JOIN CourseToState AS CS ON CS.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CS.StateID AS VARCHAR) + '','', '','' + @StateIDs + '','') > 0 '
END

If @CreditTypeIDs IS NOT NULL AND @CreditTypeIDs <> '' AND @CreditTypeIDs <> '0'
BEGIN
  SET @SQL = @SQL + 'JOIN CourseToCreditType As CC ON CC.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CC.CreditTypeID AS VARCHAR) + '','', '','' + @CreditTypeIDs + '','') > 0'
END

If @SubjectTypeIDs IS NOT NULL AND @SubjectTypeIDs <> '' AND @SubjectTypeIDs <> '0'
BEGIN
  SET @SQL = @SQL + 'JOIN CourseToSubject As CSu ON CSu.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CSu.SubjectID AS VARCHAR) + '','', '','' + @SubjectTypeIDs + '','') > 0'
END

Attempt Method 3 - Sort of Works This method only works if I send it ID's for each feld, I can't leave one blank to get "all" and I can't get it working in a stored proc, which is what I tried to do in Attempt #2

DECLARE @StateIDs VARCHAR(200) = ''
DECLARE @CreditTypeIDs VARCHAR(200) = ''
DECLARE @SubjectTypeIDs VARCHAR(200) = ''
SET @StateIDs = '1,3,2,'
SET @CreditTypeIDs = '1,3'
SET @SubjectTypeIDs = '1,2,3,4' 

SELECT DISTINCT
       C.CourseID,
       LU.FirstName,
       LU.LastName,
       (SELECT COUNT(ReviewID) FROM Review AS R WHERE R.CourseID = C.CourseID) AS ReviewCount
  FROM [Course] AS C WITH(NOLOCK)
  JOIN LexUser AS LU ON LU.LexUserID = C.PresenterID 
  JOIN CourseToState AS CS ON CS.CourseID = C.CourseID AND CHARINDEX(',' + CAST(CS.StateID AS VARCHAR) + ',', ',' + @StateIDs + ',') > 0 
  JOIN CourseToCreditType As CC ON CC.CourseID = C.CourseID AND CHARINDEX(',' + CAST(CC.CreditTypeID AS VARCHAR) + ',', ',' + @CreditTypeIDs + ',') > 0
  JOIN CourseToSubject As CSu ON CSu.CourseID = C.CourseID AND CHARINDEX(',' + CAST(CSu.SubjectID AS VARCHAR) + ',', ',' + @SubjectTypeIDs + ',') > 0

Answer 1

Try a Left Join from Widgets to all the lookup tables and filter out the appropriate rows using a Where.

Answer 2

How about something like this:

Make a stored procedure with a comma-separated list of conditions, should look like

DECLARE @colorConditions VARCHAR(100)
SET @colorConditions = ' Red, Blue, Green, Yellow, Black, Brown, '

DECLARE @sizeConditions VARCHAR(100)
SET @sizeConditions = ' Small, Med, Big, Large, '

DECLARE @weightConditions VARCHAR(100)
SET @weightConditions = ' UltraLight, Light, Normal, Heavy, UltraHeavy, '

DECLARE @SQL VARCHAR(1000)
SET @SQL = 
     'SELECT * 
      FROM Widgets W
      INNER JOIN Colors C 
        ON W.ColorID = C.ColorID AND ''' + @colorConditions + ''' LIKE ''% '' + C.Color + '', %'' 
      INNER JOIN Sizes S 
        ON W.SizeID = S.SizeID AND ''' + @sizeConditions + ''' LIKE ''% '' + S.Size + '', %''
      INNER JOIN Weight WT 
        ON W.WeightID = W.WeightID AND ''' + @weightConditions + ''' LIKE '' %'' + WT.Weight + '', %'''

EXEC (@SQL)

Nothing special here. All it does is match each trait (Color, Size, Weight) against a comma-separated list of qualifying conditions. If you want to filter on Blue items, you should pass the string ' Blue, '. The flanking spaces and commas should be present because it's easier to have them there then to fight with SQL. The condition string is then compared to each color value like so:

Condition string: 

' Blue, Green, '

Colors: 

Red
Blue
Green

...AND ' Blue, Green, ' LIKE '% Red, %' -- fails
...AND ' Blue, Green, ' LIKE '% Blue, %' -- succeeds
...AND ' Blue, Green, ' LIKE '% Green, %' -- succeeds

This must be executed dynamically, unless you split the conditions out somehow to a temporary table and use the IN operator.

Answer 3

Try this... to call it, for each parameter, pass in null to ignore, or bitmask combination of values you want included. according to following chart...

-- ----Color ----
Red    =  1
Blue   =  2
Green  =  4
Yellow =  8
Black  = 16
Brown  = 32
Red Or Green = 5

-- ---- Size ---
Small  = 1
Medium = 2
Big    = 4
Large  = 8

-- ----Weight ----
UltraLight =  1
Light      =  2
Normal     =  4
Heavy      =  8
UltraHeavy = 16

Also, change the lookup integer values in the database (both PKs in lookup tables, and FKs in the Widgets table), so that they are all the appropriate powers of two (1,2,4,,8,16... )

-- -------------------------
Create procedure GetWidgets
@Color TinyInt = Null, 
@Size TinyInt = Null
@Weight TinyInt = Null
As
Set NoCount On

-- -------------------------------

   Select * From Widgets
   Where Color & IsNull(@Color, Color)> 0
      And Size & IsNull (@Size,Size) > 0 
      And Weight & IsNull (@Weight, Weight ) > 0 

   Return 0
-- -------------------------

---

Answer 4

The prerequisite for this solution is a split table-valued function. There are many examples on the net, but I do recommend using a variant that uses a Tally or "Numbers" table as it is the fastest. Once you have that, the solution is somewhat trivial:

Declare @StateIdList nvarchar(max)
Declare @CreditTypeIdList nvarchar(max)
Declare @SubjectTypeIds nvarchar(max)

Select F1....Fn
From Course As C
Where   (
     @StateIdList Is Null 
     Or  Exists(
        Select 1
        From CourseToState As CS1
         Join dbo.Split(@StateIdList) As S1
          On S1.Id = CS1.Id
        Where CS1.CourseId = C.CourseId
        )
     )
    And (
     @CreditTypeIdList Is Null 
     Or  Exists(
        Select 1
        From CourseToCreditType As CT1
         Join dbo.Split(@CreditTypeIdList) As S1
          On S1.Id = CT1.Id
        Where CT1.CourseId = C.CourseId
        )
     )
    And (
     @SubjectTypeIds Is Null 
     Or  Exists(
        Select 1
        From CourseToSubject  As CSu1
         Join dbo.Split(@CreditTypeIdList) As S1
          On S1.Id = CSu1.Id
        Where CSu1.CourseId = C.CourseId
        )
     )