Posted by Erland Sommarskog on 06/02/05 00:30

brendan_gallagher_2001@yahoo.co.uk (brendan_gallagher_2001@yahoo.co.uk)
writes:
> I have a view(A) and I am trying to do a join on another table (B) to
> include only rows where date values in view A is greater than in table
> B. I also want the view to pick up rows in viewA based on date values.
> Here is what I have so far:
>
> SELECT *
> FROM viewA vw
> left JOIN tableB tb ON
> vw.id = tb.id and
> (vw.date1 > tb.date1 or
> vw.date2 > tb.date2 or
> vw.date3 > tb.date3)
> WHERE vw.date4 > getdate()-1
>
> Not matter what kind of join I use I can get both the rows from the
> view where dateA > getdate()-1 AND where date1-3 are greate than in
> tableB. Dates 1 - 4 seperate date fields. Could someone please tell
> me what I am doing wrong.

I will have to confess that your description of what you want and
your laments of what you get appears contradictive in conjunction
with the query. The problem with verbal descriptions is that they
are not always unambiguous.

The standard recommendation for this kind of question is that you
include:

o CREATE TABLE statement for your tables (possibly simplified) (Just
pretend that your view is a table).
o INSERT statement with sample data.
o The desired result given the samepl.

This both makes it clear what you are looking for, and makes it simple
to copy and paste into Query Analyzer to develop a tested solution.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]