Need help in query - 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)

Need help in query

Author

Topic

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-04-14 : 00:33:13

Hello Experts,

Suppose there are two tables.

Table 1 contains data like:-


ID 	BPNo	BPDate
1	100	12/03/2010
2	200	15/03/2010

Table 2:-

PymtID PymtNo	PymtAmt	PymtStatus
100	11	1500	Y
100	11	1500	N
200	22	1100	Y

Table 1 BPNO and PymtID are the common between two tables.

I want result for the pymtStatus N.

So the result i would expect like as follow:-


ID 	BPNO	BPDate		PymtID 	PymtNo	PymtAmt	PymtStatus
1	100	12/03/2010	100	11	1500	N
2	200	15/03/2010

Regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-14 : 00:37:51

[code]SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2
ON t2.PymtID = t1.BPNo
AND t2.PymtStatus='N'
[/code]

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

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-04-14 : 03:13:05

Sorry i have not posted all the details.

Please note that payamt is coming from different table3, so how to handle it?

Table 1 and Table 3 is joined with BPNO and Table3.ID.
And Table 2 and table 3 is joined with PymtNO and Table3.PymtNo.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-14 : 03:25:02

[code]
select *
from table1 t1
left join table2 t2 on t1.BPNo = t2.PymtID
and t2.PymtStatus = 'N'
left join table3 t3 on t2.PymtNo = t3.PymtNo
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-14 : 03:38:44

quote:
Originally posted by abhit_kumar

Sorry i have not posted all the details.

Please note that payamt is coming from different table3, so how to handle it?

Table 1 and Table 3 is joined with BPNO and Table3.ID.
And Table 2 and table 3 is joined with PymtNO and Table3.PymtNo.

how are table2 and table3 related to table1? Is it one to one?

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

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-04-14 : 04:12:58

TABLE 2 AND TABLE 3 HAVE THE PAYMENT DETAILS.
And the Table 3 is linked with Table 1. and table 1 is only linked with table 3 only.
And then Table 2 is linked with Table 3.And table 2 contains the data of Y and N.

Code of KHTAN will not work because he did the join between table 1 and table 2, as there is no link between it,

Table 2 doesnt link with Table 1, table 2 is linked with table 3 only.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-14 : 04:23:26

[code]
select *
from table1 t1
left join table3 t3 on t1.BPNo = t3.ID
left join table2 t2 on t2.PymtNO = t3.PymtNo
[/code]

KH
[spoiler]Time is always against us[/spoiler]

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-04-14 : 04:29:37

It will brings all the data from both the table, i want the data based upon table 2 with field Pymtstatus = N.

if it doesnt get the PymtStatus=N then bring the data from Table 1 only and display other details as NULL.

Please seem mine first post with output.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-14 : 04:30:21

quote:

Originally posted by khtan


select *
from   	table1 t1
       	left join (select required columns from t3 and t2
                   from table3 t3 
	           inner join table2 t2 
                   on  t2.PymtNO	= t3.PymtNo
                   and t2.PymtStatus = 'N'
                   )tmp
                on  t1.BPNo       	= tmp.ID

KH
[spoiler]Time is always against us[/spoiler]

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

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-04-14 : 08:37:07

THANKS VISAKH,
NOW ITS WORK.

ACTUALLY I HAVE USED OUTER APPLY IN PLACE OF LEFT JOIN.

DO THE OUTER APPLY MAKE SENSE IN IT?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources