Problems with 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)

Problems with join

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-06-11 : 10:11:07

This join is not giving me what I need.

SELECT n.Doc, c.doc AS Expr1
FROM MgmtInfo.dbo.NatDocFile2 AS n FULL OUTER JOIN
ClearedDiary AS c ON n.Doc = c.doc
WHERE (c.weekdat = '6/5/2009') AND (n.Region_ltr = 'c') AND (n.Typecode = '1' OR
n.Typecode = '13')

I want to get the records that are not in the NatDocFile2

I am getting results but not the ones I want. I tried join, and inner join but it's not giving me the records that are not in cleareddiary.

boyzone007
Starting Member

4 Posts

Posted - 2009-06-11 : 10:18:18

FULL OUTER JOIN will give all the records from both the table. If you want all the records from one table & matching records from other table , then you might need to use LEFT OUTER JOIN or RIGHT OUTER JOIN.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-11 : 10:23:45

SELECT c.doc
FROM
ClearedDiary AS c
WHERE
c.weekdat = '6/5/2009'
and
c.doc not in(select doc from MgmtInfo.dbo.NatDocFile2 where (n.Region_ltr = 'c') and typecode in ('1','13') )

Jim

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-06-11 : 10:28:18

I tried both of your statements and boyzone I get the same info as before and jimf I get null

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-06-11 : 10:30:31

jimf I tried this again but took out the not out and I still get the same amount of records. I'm looking for doc 243 in the natdocfile2. It's in there I can query that table and get it out when I go the join I can't.

SELECT c.doc
FROM
ClearedDiary AS c
WHERE
c.weekdat = '6/5/2009'
and
c.doc not in(select doc from MgmtInfo.dbo.NatDocFile2 where (n.Region_ltr = 'c') and typecode in ('1','13') )

boyzone007
Starting Member

4 Posts

Posted - 2009-06-11 : 10:57:47

I think Jimf suggestion is perfect for you. I think you need to check you c.weekdat = '6/5/2009' condition.

try to convert this field in where clause. & use '2009-06-05'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-11 : 11:06:11

Are you certain that 243 exists in ClearedDiary and does not exist in natdocfile2? If you want the docs that exist in natdocfile2 and not in ClearedDiary, just change the query around

SELECT n.doc
FROM
MgmtInfo.dbo.NatDocFile2 AS n
WHERE

n.doc not in(select doc fromClearedDiary c where c.weekdat = '6/5/2009')
and
(n.Region_ltr = 'c') and n.typecode in ('1','13')

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 11:20:43

it would have been much better if op had posted some sample data to explain what he wants

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-06-11 : 12:04:24

Sorry for not posting the data I will next time but thanks jimf this one worked!!! It was turned around 243 is in the natdocfile2 and not in cleareddiary. It gave me what I wanted. Thanks again!

SELECT n.doc
FROM
MgmtInfo.dbo.NatDocFile2 AS n
WHERE

n.doc not in(select doc from ClearedDiary c where c.weekdat = '6/5/2009')
and
(n.Region_ltr = 'c') and n.typecode in ('1','13')

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-11 : 12:28:56

You're welcome

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources