-1

i have a sql query like this:

    select something
from
(
inner query here - outputs is correct: eg 2000 datasets
) as a

left outer join tableA
on tableA.id=innerQuery.id
where  someYear = -----------> had to change this "and" to "where"
(
select max(tableYear)
from tableC
where
etc....
)

eg years:
2011, 1999, 1901 max is 2011.
1978, 1981,1990 max is 1990.

etc.. the problem i am having is, with the "where" statement, i am only getting fewer 1600 datasets; however if i were to key in a value and use "and", outputs comes out correct 2000. is there a way to use "where" with a left outer join and get all my outputs?

NULL
  • 1,559
  • 5
  • 34
  • 60
  • 1
    Can you clarify? Do you want to have the comparison be performed as part of the join condition, or as an independent condition to the final result? What table is "somedate" on? – N West Sep 27 '11 at 17:57
  • Why do you have to use "where"? Also, I don't think "datasets" is the correct terminology for what you're trying to say. You are getting 2000 rows, which makes up 1 dataset. – Tom H Sep 27 '11 at 17:58
  • yes i want the comparison to be performed as part of the left outer join since the field name and that comparison only exists in tableA and only applicable to tableA. – NULL Sep 27 '11 at 17:58
  • also the inner query values and taleA values are only common with the "ID". Meaning, there are some people in the inner query who will not exist in tableA. not vice versa. – NULL Sep 27 '11 at 18:00

3 Answers3

1

If you test a column from a LEFT JOINed table in the WHERE clause, you force that join to behave as if it were an INNER JOIN. The correct method is to make that test part of the join condition.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
1

In a LEFT JOIN, it makes a difference whether you put the filter into the JOIN clause or into the WHERE clause.

I explained the difference very detailed here:
What is the difference in these two queries as getting two different result set?

To summarize it in one sentence:
if you want the full 2000 rows and not just 1600, you have to put the filter in the JOIN clause.

Community
  • 1
  • 1
Christian Specht
  • 35,843
  • 15
  • 128
  • 182
  • this is exactly what i am looking for, however, i want to pick the max? – NULL Sep 27 '11 at 18:05
  • @Menew: Sorry, I don't get it (yet). I have seen the `select maxYear here` in your example code, but I don't understand how this relates to the joins (and what exactly is your problem now). Can you elaborate? – Christian Specht Sep 27 '11 at 18:11
  • ok my code works if i give it a max Year say 2011. this means, it will give me everyone from all Movies from 2011. however, i also want people from the inner query which is my movie db who have movies that that not 2011. – NULL Sep 27 '11 at 18:15
  • @Menew: I'm still not sure if I get it. If you give it a max. year of 2011, but you want to get back **ANY** people (no matter what year their movies are from), why do you need the max. year query at all? – Christian Specht Sep 27 '11 at 18:23
  • And, could you please clarify: in which table is the `someYear` column that you are trying to filter? In `innerQuery` or `tableA`? – Christian Specht Sep 27 '11 at 18:24
  • I was missing isNull check on someYear! – NULL Sep 27 '11 at 18:29
0

Why not leave the condition as part of the join (as you have posted? somedate must be from the right table i.e. tableA and so it may be null. So if you want to get all the results, you have to account for that e.g.

IsNull(somedate, '1/1/2000') = '1/1/2000'

or

(somedate = '1/1/2000' OR somedate is null)
amit_g
  • 30,880
  • 8
  • 61
  • 118
  • ok so i added this to my query and it works (somedate is null)! many thanks, i overlooked your answer! – NULL Sep 27 '11 at 18:25