I have 2 tables: user and licenseduser. The first one contains all the users and the second one contains only those users with a licenses (2 columns: useruid and subscriptionid).
Given a subscriptionId I have to display in a grid all the users and a boolean flag column indicating for each user if he/she has the subscription or not.
Example: The following query gives me that for the subscriptionId = 7.
select firstname, lastname, emailaddress, subscriptionid
from dbo.[user]
left join (select * from dbo.licensedUser where subscriptionid = 7) lu on dbo.[user].[Uid] = lu.useruid
However, this query is not useful for me because the executor program uses an ORM. I want to create a view (called myView) such that I can (or the ORM can) do this:
select firstname, lastname, emailaddress, subscriptionid
from myView
where subscriptionid = 7 or subscriptionid is null
Could you help me, please? Thank you in advance.
If you have a table of subscriptions, you can use this:
CREATE VIEW
v_user_subscription
SELECT u.*, s.subscriptionId, lu.subscriptionId AS subscribed
FROM user u
CROSS JOIN
subscription s
LEFT JOIN
licensedUser lu
ON lu.userId = u.uid
AND lu.subscriptionId = s.subscriptionId
and use this:
SELECT firstname, lastname, emailaddress, subscribed
FROM v_user_subscription
WHERE subscriptionId = 7
in the ORM.
You don't want to select from this view without filtering, as it will probably be huge, however, with a filter on subscriptionId
SQL Server
will expand the view and won't actually browse other subscriptions.