Restating -- Conditional SubQuery in WHERE.

  • Thread starter Thread starter Bob T
  • Start date Start date
B

Bob T

Hi All,

I wasn't very clear before. I am trying to see if there is a way to have
conditional subqueries in the WHERE part of an SQL statement. Generally I
am trying to return rows matched to the subquery when there are rows
returned by the subquery. No problem using IN. What I want is,, If there
are no rows in the subquery returned, I want to see all rows in the outer
select. In other words I would like to turn off this portion of the WHERE
statement (or trick it to have no impact in the WHERE.

EX/
Alter Procedure PrefProv @UserID VarChar(50), @Flag BIT

SELECT Name, Address1, HCity
FROM table1
WHERE table1.ID IN (SELECT Table1ID FROM dbo.table2 WHERE UserID = @UserID)
AND Flag = @Flag


The subquery will return Table1ID 's that match the UserID = @UserID, Then
table1 columns where ID is in the list of Table1ID 's returned by the
subquery AND if Flag = @Flag.
If the table2 subquery returns no records I would like the statement to act
like.

SELECT Name, Address1, HCity
FROM table1
WHERE Flag = @Flag


In other words disable the portion of the WHERE which is related to the
subquery.

I have tried CASE but CASE doesn't want to work with IN. CASE works fine
with =,<,> or LIKE.
I have also looked at EXISTS but the examples in Books Online show
equivalent IN statements.

I could (and may have to) use IF..ELSE and change or leave out various WHERE
terms but I have many variations and would make for a very complex PROC.

Also if someone could explain EXISTS to me and how it may differ from IN I
would appreciate it.
From Books Online:
The EXISTS keyword is important because often there is no alternative,
nonsubquery formulation. Although some queries formulated with EXISTS cannot
be expressed any other way, all queries that use IN or a comparison operator
modified by ANY or ALL can be expressed with EXISTS.

Bob T
 
EXISTS i just to test that you have at least one row while IN check for rows
that have a value in the specified set.

Your query could be something like :

SELECT * FROM a WHERE NOT EXISTS(SELECT 1 FROM b WHERE condition) OR key in
(SELECT key FROM b where condition)

If you have no matching rows in b, the first part is true and all rows are
returned
If you have matching rows, the first part is false and only the second part
of the OR that determines the final result...

(Though it looks a bit weird to have a SP that returns all if nothing should
be returned from an architectural point of view i.e you'll have no way from
the outside to know wether you have all the records because nothing meets
the criteria or if you have all the records because they all match).

Patrice
 
If I understand you correctly, won't an Or instead of AND do the job? If
nothing is returned in the subquery, you'll grab everything where Flag =
@Flag (either way you'll get these records, but that's what you want isn't
it?)

Alter Procedure PrefProv @UserID VarChar(50), @Flag BIT

SELECT Name, Address1, HCity
FROM table1
WHERE table1.ID IN (SELECT Table1ID FROM dbo.table2 WHERE UserID = @UserID)
OR Flag = @Flag

I think you can do SELECT Name, Address1, HCity
From Table1
Where (Not Exists(SELECT Table1ID FROM dbo.table2 WHERE UserID = @UserID)
AND Flag = @Flag) OR
Exists(SELECT Table1ID FROM dbo.table2 WHERE UserID = @UserID)


If I understood you correctly, I think these should work depending on if you
want Flag=@Flag either way or not.

Cheers,

Bill
 
Thanks to both Patrice and William.

Patrice your approach looks like it will work though I am still trying more
WHERE terms.

William, the second one looks like it work with the additional terms. The
first WHERE with the straight OR only would return too many IDs if some of
their Flags were FALSE (when looking for TRUE)

Thanks again.

Bob T
 
Back
Top