Use SQL query with a join clause in a view

Question

Is it possible to create a database view that emulates the select query below?

select
 A.Name,
 B.Subscription
from
 TestTableA as A left outer join TestTableB as B on A.ID = B.A_ID and Subscription = 'xxxx';

With a view I can't see how I can filter the Subscription in the join clause and instead can only have it as a condition on the query's where clause.

(A more involved version of this question).

Update:

TestTableA

ID | Name
---|------
 1 | Joe
 2 | Jane

TestTableB

ID | A_ID | Subscription
---|------|-------------
 1 | 1    | abcd
 2 | 1    | efgh

Query Results:

Using the above "select" statement the result set is:

Name | Subscription
-----|-------------
Joe  | null
Jane | null

What I'm trying to achieve is to create a view that returns me the same thing.

select * from MyView where Subscription is null Subscription = 'xxxx';

Currently all I can achieve with a view and the query immediately above is as below. I have no way to get "Joe" into the results with a null subscription.

Name | Subscription
-----|-------------
Jane | null

What's the difference between this and your other topic? – Kermit Oct 18 '12 at 00:25 — Kermit, Oct 18 '12 at 00:25
It's a simpler question and more specific. – sipsorcery Oct 18 '12 at 00:26 — sipsorcery, Oct 18 '12 at 00:26
Could you have edited your original question? – Kermit Oct 18 '12 at 00:28 — Kermit, Oct 18 '12 at 00:28

score 0 · Accepted Answer · answered Oct 18 '12 at 03:00

0

I found a way to do it by using a union in the view.

create or replace view TestView as
  select
   A.Name,
   null as Subscription
  from
    TestTableA as A left outer join TestTableB as B on A.ID = B.A_ID
  union
   select
    A.Name,
    B.Subscription
  from
   TestTableA as A left outer join TestTableB as B on A.ID = B.A_ID;

And then query the view using the statement below gives me the results I want:

 select * from TestView where Subscription is null or Subscription = 'xxxx';

answered Oct 18 '12 at 03:00

sipsorcery

30,273
24
104
155

That's still not equivalent and may not generate the result you want in all cases. Consider the result when you have the following: select * from TestView where Subscription is null or Subscription = 'efgh'; See the following sqlfiddle: http://sqlfiddle.com/#!2/07099/2 – Jon Armstrong - Xgc Oct 19 '12 at 06:42
I agree it's not equivalent but it's the closest I could come up with. It means extra work in the application's business layer code but that's better than not getting all the records. – sipsorcery Oct 19 '12 at 11:47
Why use a view if it just obfuscates the real logic? Just use the direct outer join from your application, not a view which does not have the same meaning. You could even hide this behind a layer (in your application) that takes the 'xxxx' argument, uses that as an argument in the real outer join and then returns the result to the caller. Some problems are best addressed in the application and some in the database. Alternative to follow... – Jon Armstrong - Xgc Oct 19 '12 at 12:05
An alternative is to use a concept called a prepared statement, where the JOIN logic would be written like this: t1 LEFT JOIN t2 ON t1.blah = t2.blah AND t2.subscription = ?; For each request, the application binds an argument to the (?) parameter and then executes the statement. The form of the statement doesn't change, just the bound argument does. – Jon Armstrong - Xgc Oct 19 '12 at 12:06
I'm effectively using the direct outer join in my application already but because it's called under the Entity Framework the overall query has become too slow. By using a view I could hand optimise the query and speed it up. Unfortunately the logic required is still proving too unwieldy to fit into a view and a stored procedure would mean the no server side filtering with a where clause. – sipsorcery Oct 19 '12 at 20:18

Use SQL query with a join clause in a view

1 Answers1