RE: How to join two tables and only display the values on the first ro

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 12/20/04 

Date: Mon, 20 Dec 2004 12:15:01 -0800

Better to do this in the reporting tool you are using.

use northwind
go

create table t1 (
colA int not null identity unique,
colB char(1) not null,
colc char(1) not null,
colD char(2) not null
)

insert into t1 (colB, colC, colD) values('A', 'B', 'N1')
insert into t1 (colB, colC, colD) values('C', 'D', 'N1')
insert into t1 (colB, colC, colD) values('E', 'F', 'N1')
insert into t1 (colB, colC, colD) values('G', 'H', 'N2')
insert into t1 (colB, colC, colD) values('I', 'J', 'N2')
insert into t1 (colB, colC, colD) values('K', 'L', 'N2')

create table t2 (
colA char(2) not null,
colB int not null
)

insert into t2 values('N1', '400')
insert into t2 values('N2', '600')
go

select
        t1.colA,
        t1.colB,
        t1.colC,
        nullif(t2.colB, (select a.colB from t2 as a where a.colA = (select b.colD
from t1 as b where b.colA = t1.colA - 1)))
from
        t1
        inner join
        t2
        on t1.colD = t2.colA
order by
        1
go

drop table t1, t2
go

AMB

"Alfredo Becerril" wrote:

> Hi all,
>
> I need to join two tables but the joined table must be displayed only in
> the first group's row...
>
> Table 1
> FieldID Value1 Value2 LookupField
> 1 A B N1
> 2 C D N1
> 3 E F N1
> 4 G H N2
> 5 I J N2
> 6 K L N2
>
> Table 2
>
> FieldID Value3
> N1 400
> N2 600
>
>
> I want the following result
>
> Table 1 join Table 2
> FieldID Value1 Value2 Value3
> 1 A B 400
> 2 C D NULL
> 3 E F NULL
> 4 G H 600
> 5 I J NULL
> 6 K L NULL
>
> Thanks in advance
>
> Alfredo
>
>
>

Relevant Pages

  • RE: IDENTITY_INSERT problem
    ... Thanks a lot Alejandro Mesa for your response. ... > use northwind ... > create table t (colA int not null identity unique) ...
    (microsoft.public.sqlserver.programming)
  • RE: Counting sequencial rows
    ... use northwind ... colA int not null unique, ... colB varcharnot null ... insert into t values(356, 'Mark') ...
    (microsoft.public.sqlserver.programming)
  • RE: select comma separated values in column
    ... use northwind ... colA int not null identityunique, ... insert into t (colB) values ... every single listitem ...
    (microsoft.public.sqlserver.programming)
  • RE: Simple Query
    ... colB varchar, ... > ID Nam age ...
    (microsoft.public.sqlserver.programming)
  • Re: Scope_Identity syntax question
    ... INSERT tblScheduleLog ... use northwind ... colA int not null identityunique, ... colB varcharnot null ...
    (microsoft.public.sqlserver.programming)