Conditional Statement 1

[ryan]

Ok heres the deal I have one table that is like this called Licenses:

App License
-----------------
1 4585
2 4587
3 5214

and another like this called UserToLicenses

User License
------------------
no data

How can I do a query that basicly does this: I need to return a license for an app id that I specifiy but I want to return the license if the user matches the license or if it does not meaning if there is no data in the UsersToLicenes table then I just get back the license that matches the supplied appid but if a user is in the UserToLicenses table then I want to get that license which is also based on the appid.

here's a little more visual ....

Basically I want this if I got data ...

Select Licenses.License From Licenses, UserToLicenses
Where Licenses.AppID = 1
And UserToLicenses.UserID = 1

if no data out of that query then run this...

Select License From Licenses Where AppID = 1

Thanks ... this problem is killing me.

 

[tlbroadbent]

Create a stored procedure and pass it two parameters.
[tt]
Create Procedure SelectLicense @AppID int, @UserID int As

If Exists
(Select l.License
From Licenses As l Inner Join UserToLicenses As u
On l.License=u.License
Where Licenses.AppID = 1
And UserToLicenses.UserID = 1)

Begin

Select l.License
From Licenses As l Inner Join UserToLicenses As u
On l.License=u.License
Where Licenses.AppID = 1
And UserToLicenses.UserID = 1

End

Else

Begin

Select License From Licenses Where AppID = 1

End
[/tt]
Execute the procedure like this...

Exec SelectLicense 1,1 Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[rajeevnandanmishra]

Hi ryank,
Just thought to do all this in other way
SELECT b.License
FROM Licenses b LEFT OUTER JOIN UserToLicenses a
ON b.License=a.License AND a.userid=myUserId
WHERE b.app=myAppId AND
(CASE WHEN a.License <> b.License THEN 1
ELSE (CASE a.USERID WHEN myUserId THEN 1 ELSE 0 END) END)=1


Let me know if it works and give you result as expected.

 

[ryan]

Terry that worked great. Thanks alot!

rayeevnandanmishra - yours worked too but it didn't pull anything when there is no data in the second table ... any ideas?

Thanks,
Ryan