twisted self-joining link - 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

General SQL Server Forums

New to SQL Server Programming

twisted self-joining link

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-02-14 : 11:30:07

I have a table and the columns Voucher and Settlevoucher.

The query:

select VOUCHER, SETTLEVOUCHER from table WHERE VOUCHER ='FRCXXXX'

returns exactly one result with SETTLEVOUCHER='COBXXXX' OR NULL with VOUCHER being singular for all FRC-numbers

In the second step I query what was the result of query1:

select VOUCHER, SETTLEVOUCHER from table WHERE VOUCHER ='COBXXXX'

this gives me a non predictible amount of results, because VOUCHER is not singular for the COB-numbers.

As I'm trying to do this 2 steps in one, I am missing results and I do not understand why.

Select t.VOUCHER, t.SETTLEVOUCHER
FROM table LEFT OUTER JOIN table as t ON table.SETTLEVOUCHER=t.VOUCHER
WHERE table.VOUCHER ='FRCXXXX'

No matter if I do an inner join, left or right outer join the result is always only one, while it should be what returns query2 taken separately.

What am I do wrong?
Martin

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-14 : 12:05:17

well how do you want the results to look like? Hard for use to give you a solution without it..we can only guess


select VOUCHER, SETTLEVOUCHER from table WHERE VOUCHER ='FRCXXXX'
UNION ALL
select VOUCHER, SETTLEVOUCHER from table WHERE VOUCHER ='COBXXXX'

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-02-14 : 12:10:20

I think this is what you want

Select	t1.VOUCHER, t2.SETTLEVOUCHER
FROM	[table] t1
JOIN	[table] t2
ON	t2.VOUCHER = t1.SETTLEVOUCHER
WHERE	t1.VOUCHER ='FRCXXXX'

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-02-14 : 12:36:41

Hurrah, thank you Russell... it works, but I still do not understand why it does... and why mine didn't! Guess I have to stop for today!

Brett, I did not want "COBXXXX" to be part of the query...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 13:45:45

quote:
Originally posted by barnabeck

Hurrah, thank you Russell... it works, but I still do not understand why it does... and why mine didn't! Guess I have to stop for today!

Brett, I did not want "COBXXXX" to be part of the query...

reason is here

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources