SQL Server CTE select on multiple conditions

Question

Just wondering the right way of doing it, I can't use "if" in following situation, and I need to add more conditions on it as well.

I can do that by adding union but it's not efficient and I don't want to check exists multiple times then select.

Any OPTIMAL way to do it? Thanks

I want to select based on what it matches.

 declare @studentID varchar(50) = '1432166';
 declare @firstname varchar(50) = 'Hello';
 declare @emailAddress varchar(100) = 'abc@hello.com';
 declare @mobileNumber varchar(10) = '2312321'

 with byStudentID as 
 (
      select 
          U3l_referenceID,
          preferredfirstname, 
          studentID,
          emailAddress,
          mobileNumber
      from 
          [u3_data].[data].[ListData_3bf154c40aa84565b9bb08d58ffffff6] L with(nolock)
      where 
          studentID = @studentID
),
byfNameEmailAdd as 
(
    select 
        U3l_referenceID,
        preferredfirstname, 
        studentID,
        emailAddress,
        mobileNumber
    from 
        [u3_data].[data].[ListData_3bf154c40aa84565b9bb08d58ffffff6] L with(nolock)
    where 
        preferredfirstname = @firstname 
        and emailAddress = @emailAddress 
),
byMobileNumber as  
(
    select 
        U3l_referenceID,
        preferredfirstname, 
        studentID,
        emailAddress,
        mobileNumber
    from 
        [u3_data].[data].[ListData_3bf154c40aa84565b9bb08d58ffffff6] L with(nolock)
    where 
        mobileNumber = @mobileNumber 
        and emailAddress = @emailAddress 
)
IF Exists (select * from byStudentID)
    select * from byStudentID
End
else IF Exists (Select * from byfNameEmailAdd)
    select * from byfNameEmailAdd
End
else if Exists (Select * from byMobileNumber)
    select  * from byMobileNumber
End

@mac_s I would be very helpful if you could help me out on [this](https://stackoverflow.com/q/53372997/7124761) — Prashant Pimpale, Nov 20 '18 at 05:52

score 1 · Answer 1 · answered Nov 20 '18 at 16:46

I just noticed that these are all coming from the same table, so you can use a single query with nested OR statements:

SELECT  [SearchType]    = 'StudentID'
    , [U3l_referenceID]
    , [preferredfirstname]
    , [studentID]
    , [emailAddress]
    , [mobileNumber]
FROM    [u3_data].[data].[ListData_3bf154c40aa84565b9bb08d58ffffff6] [L] WITH (NOLOCK)
WHERE ([studentID] = @studentID)
    OR
    (
        [preferredfirstname] = @firstname
        AND [emailAddress] = @emailAddress
    )
    OR
    (
        [mobileNumber] = @mobileNumber
        AND [emailAddress] = @emailAddress
    ) ;

score 0 · Answer 2 · answered Nov 20 '18 at 01:05

You can use UNION ALL to pull any results found by any method. Here I added a new [SearchType] field just so you can tell where the results came from:

 DECLARE @studentID VARCHAR(50) = '1432166';
 DECLARE @firstname VARCHAR(50) = 'Hello';
 DECLARE @emailAddress VARCHAR(100) = 'abc@hello.com';
 DECLARE @mobileNumber VARCHAR(10) = '2312321'

  SELECT 
  [SearchType] = 'StudentID',
  [U3l_referenceID],
  [preferredfirstname], 
  [studentID],
  [emailAddress],
  [mobileNumber]
    FROM [u3_data].[data].[ListData_3bf154c40aa84565b9bb08d58ffffff6] [L] WITH(NOLOCK)
    WHERE [studentID] = @studentID
UNION ALL
  SELECT 
  [SearchType] = 'FN.Email',
  [U3l_referenceID],
  [preferredfirstname], 
  [studentID],
  [emailAddress],
  [mobileNumber]
    FROM [u3_data].[data].[ListData_3bf154c40aa84565b9bb08d58ffffff6] [L] WITH(NOLOCK)
    WHERE [preferredfirstname] = @firstname 
        AND [emailAddress] = @emailAddress
UNION ALL
  SELECT 
  [SearchType] = 'MN.Email',
  [U3l_referenceID],
  [preferredfirstname], 
  [studentID],
  [emailAddress],
  [mobileNumber]
    FROM [u3_data].[data].[ListData_3bf154c40aa84565b9bb08d58ffffff6] [L] WITH(NOLOCK)
    WHERE [mobileNumber] = @mobileNumber 
        AND [emailAddress] = @emailAddress

If you only need one answer, just wrap the whole thing in a CTE and then select TOP 1 from it.

thanks it will work. but im looking for a more elegant way to do it. thanks, — Dev, Nov 20 '18 at 01:22

SQL Server CTE select on multiple conditions

2 Answers2