CODEWITHSUNDEEP
sqlsql-servert-sql
Casey Crookston
Casey Crookston

Reputation: 13945

SELECT * FROM a subquery which beings with IF EXISTS

Can't seem to figure this out.

The table OrganizationUserSettings has two rows where SettingSys is 3 and OrganizationSys is 1012.

There will always be a row where OrgUserSettingsProfileSys is null. There might be a row (or many rows) where it has a value.

Given a parameter @OrgUserSettingsProfileSys, I need a row that matches if it exists. If there isn't one, then I need the row where it's null.

enter image description here

SELECT * FROM
(
    IF EXISTS (Select * FROM OrganizationUserSettings WHERE OrgUserSettingsProfileSys = @OrgUserSettingsProfileSys)
    BEGIN 
        SELECT * FROM OrganizationUserSettings WHERE OrgUserSettingsProfileSys = @OrgUserSettingsProfileSys
    END
    ELSE
    BEGIN
        SELECT * FROM OrganizationUserSettings WHERE OrgUserSettingsProfileSys IS NULL
    END
)
WHERE SettingSys = 3 AND OrganizationSys = 1012

With this, I get

Incorrect syntax near 'IF'

EDIT:

I've simplified this query for the sake of posting here. I need this to be part of a subquery.

Upvotes: 0

Views: 123

Answers (6)

sticky bit
sticky bit

Reputation: 37472

A simple OR should work, either checking, that the value exists and the row has the value or, that the value doesn't exist and the row has a null value.

SELECT *
       FROM OrganizationUserSettings
       WHERE (EXISTS (SELECT *
                             FROM OrganizationUserSettings
                             WHERE OrgUserSettingsProfileSys = @OrgUserSettingsProfileSys)
              AND OrgUserSettingsProfileSys = @OrgUserSettingsProfileSys
               OR NOT EXISTS (SELECT *
                                     FROM OrganizationUserSettings
                                     WHERE OrgUserSettingsProfileSys = @OrgUserSettingsProfileSys)
                  AND OrgUserSettingsProfileSys IS NULL)
             AND SettingSys = 3
             AND OrganizationSys = 1012;

Upvotes: 1

JamieD77
JamieD77

Reputation: 13949

you can use top 1 with ties to get more than one row if there are multiple rows that are not null

select      top 1 with ties
            *
from        OrganizationUserSettings
where       SettingSys = 3
and         OrganizationSys = 1012
and         (   OrgUserSettingsProfileSys is null
          or    OrgUserSettingsProfileSys = @OrgUserSettingsProfileSys)
order by    case
                when OrgUserSettingsProfileSys is null then 2
            else 1
            end

Upvotes: 3

Ronaldo Cano
Ronaldo Cano

Reputation: 925

this should work:

   IF EXISTS
(
    SELECT *
    FROM OrganizationUserSettings
    WHERE OrgUserSettingsProfileSys = @OrgUserSettingsProfileSys
)
BEGIN
    SELECT *
    FROM
    (
        SELECT *
        FROM OrganizationUserSettings
        WHERE OrgUserSettingsProfileSys = @OrgUserSettingsProfileSys
    ) AS x
    WHERE SettingSys = 3
          AND OrganizationSys = 1012;

END;
ELSE
BEGIN
    SELECT *
    FROM
    (
        SELECT *
        FROM OrganizationUserSettings
        WHERE OrgUserSettingsProfileSys IS NULL
    ) AS x
    WHERE SettingSys = 3
          AND OrganizationSys = 1012;
END;

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31775

You can't put an IF inside a query like this.

One way you can do what you seem to be trying to do is this way:

IF EXISTS(SomeQuery)
   SELECT Query A
ELSE
   SELECT Query B

A way to make a subquery that does what you seem to want to do is this:

SELECT * FROM (
  SELECT * FROM MyTable WHERE SomeColumn IS NOT NULL AND OtherConditions
  UNION ALL
  SELECT * FROM MyTable 
  WHERE SomeColumn IS NULL AND OtherConditions
  AND NOT EXISTS ( SELECT * FROM MyTable WHERE SomeColumn IS NOT NULL AND OtherConditions)
) sq

Upvotes: 2

DIWP
DIWP

Reputation: 181

 SELECT top 1 * 
FROM OrganizationUserSettings 
WHERE SettingSys = 3 AND OrganizationSys = 1012
and (OrgUserSettingsProfileSys = @OrgUserSettingsProfileSys or OrgUserSettingsProfileSys is NULL)
order by OrgUserSettingsProfileSys desc

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is a weird query. Perhaps you intend:

WITH t as (
      SELECT *
      FROM OrganizationUserSettings
      WHERE OrgUserSettingsProfileSys = @OrgUserSettingsProfileSys
     )
SELECT *
FROM ((SELECT t.*
       FROM t
      ) UNION ALL
      (SELECT *
       FROM OrganizationUserSettings
       WHERE OrgUserSettingsProfileSys IS NULL AND
             NOT EXISTS (SELECT 1 FROM t)
      )
     ) t
WHERE SettingSys = 3 AND OrganizationSys = 1012;

If you are expecting only one row to match, then this is more simply written as:

WITH t as (
      SELECT TOP (1) *
      FROM OrganizationUserSettings
      WHERE OrgUserSettingsProfileSys = @OrgUserSettingsProfileSys OR
            OrgUserSettingsProfileSys IS NULL
      ORDER BY (CASE WHEN OrgUserSettingsProfileSys IS NOT NULL THEN 1 ELSE 2 END)
     )
SELECT *
FROM t
WHERE SettingSys = 3 AND OrganizationSys = 1012;

Upvotes: 2

Related Questions