Can a where clause on a join be used in a view

Question

I'm attempting to create an SQL view that consolidates a number of separate select queries. I've encountered some difficulty putting clauses from the individual select statements into the database view.

A simplified version of my view is:

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

I've got two problems with the view:

On the frist join how can I only select record where the Subscription is a specific value AND if it is not that value still retrieve the Name and Expiry columns (in which case the Subscription would be null)?
On the second join how can I specify I only want the record with the most recent expiry date?

Below is my test schema, sample data and desired result set:

create table TestTableA
(
    ID int,
    Name varchar(32),
    Primary Key(ID)
);

create table TestTableB
(
    ID int,
    A_ID int,
    Subscription varchar(32),
    Primary Key(ID),
    Foreign Key(A_ID) references TestTableA(ID)
);

create table TestTableC
(
    ID int,
    A_ID int,
    Expiry date,
    Primary Key(ID),
    Foreign Key(A_ID) references TestTableA(ID)
);

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

insert into TestTableA values (1, 'Joe');
insert into TestTableB values (1, 1, 'abcd');
insert into TestTableB values (2, 1, 'efgh');
insert into TestTableC values (1, 1, '2012-10-25');
insert into TestTableC values (2, 1, '2012-10-24');
insert into TestTableA values (2, 'Jane');

Desired Results 1:

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

Joe, efgh, 2012-10-25
Jane, ,

Desired Results 2:

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

Joe, , 2012-10-25
Jane, ,

score 2 · Accepted Answer · answered Oct 17 '12 at 05:29

I'll write query with simple SQL
If you have SQL Server 2005 or higher, you can use outer apply instead of join on subquery with min()

select 
    A.Name,
    B.Subscription,
    C.Expiry
from TestTableA as A
    left outer join TestTableB as B on A.ID = B.A_ID and B.Subscription in ('abcd', 'efgh') 
    left outer join
    (
        select min(T.Expiry) as Expiry, T.A_ID
        from TestTableC as T
        group by T.A_ID
    ) as C on A.ID = C.A_ID

score 0 · Answer 2 · answered Oct 17 '12 at 05:31

create or replace view TestView as
select 
  A.Name,
  B.Subscription,
  C.Expiry
from
  TestTableA as A left outer join TestTableB as B on A.ID = B.A_ID
  left outer join TestTableC as C on A.ID = C.A_ID;
where 
  B.Subscription is not null
  and C.Expiry between (now() - interval 1 minute) and now()

Can a where clause on a join be used in a view

2 Answers2

Linked