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.
Author |
Topic |
mmkrishna1919
Yak Posting Veteran
95 Posts |
Posted - 2013-03-14 : 10:17:50
|
Hi All,I have two tablestable 1:cno pno vno100 10 001100 10 002 100 10 003200 20 001200 21 001200 22 001table 2:cno amt100 1245200 1500i join these two tables based on cno i will get 6 rows in result set.but my expected result set is:cno pno vno cno amt100 10 001 100 1245200 20 001 200 1500i need reocrds with cno=01 from table2 i tried with below way,is it correct or any other best approach is there?select * from table1 t inner join table2 t2on t.cno=t2.cnoand t.vno=(select min(t3.vno) from tabel1 t3 where t.cno=t3.cno and t.pno=t3.pno)and t.pno=(select min(t3.pno) from table1 t3 where t.cno=t3.cno and t.vno=t3.vno)Thanks..M.MURALI kRISHNA |
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2013-03-14 : 16:59:03
|
do you need records only for vno=001 and pno = 20, 10? then select a.*, b.* from table1 a join table2 bon a.cno = b.cnoand a.vno = '001'and a.pno in (10, 20) |
|
|
|
|
|