SQL How to extract a predicate from a left join

Question

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.

Is `subscriptionId` a foreign key to some kind of subscriptions table? — Quassnoi, Nov 01 '12 at 20:09

score 1 · Accepted Answer · answered Nov 01 '12 at 20:18

1

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.

answered Nov 01 '12 at 20:18

Quassnoi

413,100
91
616
614

This is exactly what I was looking for. And you´re right I will always select from this view with a filter for one specific subscriptionId. Is there something extra that I could do in order to improve this query performance? Thank you! – lontivero Nov 01 '12 at 22:38
@lontivero: just make sure you have a `UNIQUE` index (or `PRIMARY KEY`) on `licensedUser (uid, subscriptionId)`. – Quassnoi Nov 02 '12 at 05:02
+1 I've thought about this cross join solution but decide to go for simple join :) – Roman Pekar Nov 02 '12 at 08:10

SQL How to extract a predicate from a left join

1 Answers1