Question about a left join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Question about a left join

Author

Topic

elpeak
Starting Member

5 Posts

Posted - 2007-08-27 : 14:39:55

This is a 2 table scenario. I'm pulling records from t1 left joining to t2 on a certain record. I was looking to recieve all those records that match the criteria of the join from t1 and just the ones that match t1 from t2. It seems not to show all the records of t1, because their are nulls in t2. Anyone care to elaborate or advise. Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-27 : 14:43:22

Post the query and some sample data from each table, and what you were expecting to retrieve.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 14:54:49

SELECT *
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 AND <t2 filter here only>
WHERE <t1 filer only her>

Please post full query and we might help you better

E 12°55'05.25"
N 56°04'39.16"

elpeak
Starting Member

5 Posts

Posted - 2007-08-27 : 15:16:19

SELECT t1.id, t2id
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 on t1.id = t2.id
WHERE month(t1.proc_date) = month(getdate()) -1 and year(t1.proc_date) = year(getdate())

looking for the records in t1 and not in t2 for this date period

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 15:33:11

Please post sample data and your expected output.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-28 : 03:01:21

quote:
Originally posted by elpeak

SELECT t1.id, t2id
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 on t1.id = t2.id
WHERE month(t1.proc_date) = month(getdate()) -1 and year(t1.proc_date) = year(getdate())

looking for the records in t1 and not in t2 for this date period

Where condition should be

WHERE
t1.proc_date>=dateadd(month,datediff(month,0,getdate())-1,0) and
t1.proc_date<dateadd(month,datediff(month,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

elpeak
Starting Member

5 Posts

Posted - 2007-08-28 : 11:07:25

the where clause is essentially the same. Syntax differently. On that note though, when the year changes how will you handle it in the where clause? Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 11:09:50

It will work excellent, which you will notice if you cared to try it.

E 12°55'05.25"
N 56°04'39.16"

elpeak
Starting Member

5 Posts

Posted - 2007-08-28 : 11:43:41

Test the code and that is a true statement. Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-28 : 12:10:13