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 |
|
deadfish
Starting Member
38 Posts |
 Posted - 2003-01-13 : 11:32:34
|
| I have a problem with outer join....Here are the tables I have:table a:ref_no seq_no desc------------------a1 1 aaaaa1 2 bbbba1 3 cccca2 1 ddddtable b:ref_no seq_no desc2-------------------a1 2 eeeea1 3 ffff a2 1 ggggI need to have the result as following:ref_no seq_no desc desc2------------------------a1 1 aaaa (null)a1 2 bbbb eeeea1 3 cccc ffffa2 1 dddd gggg I wrote the following but seems it doesn't work...select a.ref_no, a.seq_no, a.desc, b.desc2 from a left outer join bon a.seq_no = b.seq_noand a.ref_no = b.ref_noHow to get this work??Thx! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-13 : 11:45:25
|
| What are the results you get from that query? How do they differ from what you wanted to get? |
 |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-01-13 : 11:46:18
|
| Try something like this...create table #moo(ref varchar(2), seq int, descr varchar(6))insert into #moo values ('a1',1,'aaaa')insert into #moo values ('a1',2,'bbbb')insert into #moo values ('a1',3,'cccc')insert into #moo values ('a2',1,'dddd')create table #moo2(ref varchar(2), seq int, descr2 varchar(6))insert into #moo2 values ('a1',2,'eeee')insert into #moo2 values ('a1',3,'ffff')insert into #moo2 values ('a2',1,'gggg')select a.ref, a.seq,--a.descr, b.descr2max (case when a.descr is not null then a.descr else null end) as descr, max ( case when b.descr2 is not null then b.descr2 else null end) as descr2from #moo a left outer join #moo2 b on a.seq = b.seqand a.ref = b.refgroup by a.ref, a.seqorder by a.refdrop table #moodrop table #moo2The key bits being the max and group bys.-------Moo. |
|
|
|
|
|
|
|
|
problem about outer join