Numbering the records in the many part of a join

15 Jul 2005 10:45 AM

Chris Strug

Hi,

Probably a silly question but given the statement

SELECT     B.Booking_Number, B.Job_Status, A.Address_1, A.Address_2,
A.Address_3, A.Address_4, A.Address_Postcode
FROM         dbo.tblAddress A RIGHT OUTER JOIN
                      dbo.tblBooking B ON A.Booking_ID = B.Booking_ID

where tblBooking (B) is the one side of the relation and table Address (A)
is the many side, how do I number the address records for each Booking
record?

For example,

Booking A has three address, hence they will be numbered 1, 2, 3
Booking B has one address which will be numbered 1,
Booking C has two addresses which will be numbered 1, 2

I.e.
Booking Ref    Booking_Data    Address_Data    Address_No
A                    Info                     Add_Info2                   1
A                    More Info            Add_Info                     2
A                    And more            Add_Info3                   3
B                    data3                   Add_Data                    1
C                    Data4                  Add_Data4                  1
C                    Data5                  Add_Data5                  2

I'm really not sure how to achieve this, if anyone could provide any
pointers I'd be very grateful.

Kind thanks

Chris.

15 Jul 2005 11:47 AM

John Bell

Hi Chris

If you have an identifier for the address then something like:

SELECT B.Booking_Number, B.Job_Status, A.Address_1, A.Address_2,
A.Address_3, A.Address_4, A.Address_Postcode,
ISNULL(( SELECT COUNT(*) FROM dbo.tblAddress C WHERE A.Booking_ID =
C.Booking_ID AND A.Address_ID >= C.Address_ID ),1) AS Address_no
FROM dbo.tblBooking B
LEFT JOIN dbo.tblAddress A ON A.Booking_ID = B.Booking_ID

John

Show quote

"Chris Strug" wrote:

> Hi,
>
> Probably a silly question but given the statement
>
> SELECT     B.Booking_Number, B.Job_Status, A.Address_1, A.Address_2,
> A.Address_3, A.Address_4, A.Address_Postcode
> FROM         dbo.tblAddress A RIGHT OUTER JOIN
>                       dbo.tblBooking B ON A.Booking_ID = B.Booking_ID
>
> where tblBooking (B) is the one side of the relation and table Address (A)
> is the many side, how do I number the address records for each Booking
> record?
>
> For example,
>
> Booking A has three address, hence they will be numbered 1, 2, 3
> Booking B has one address which will be numbered 1,
> Booking C has two addresses which will be numbered 1, 2
>
> I.e.
> Booking Ref    Booking_Data    Address_Data    Address_No
> A                    Info                     Add_Info2                   1
> A                    More Info            Add_Info                     2
> A                    And more            Add_Info3                   3
> B                    data3                   Add_Data                    1
> C                    Data4                  Add_Data4                  1
> C                    Data5                  Add_Data5                  2
>
> I'm really not sure how to achieve this, if anyone could provide any
> pointers I'd be very grateful.
>
> Kind thanks
>
> Chris.
>
>
>

15 Jul 2005 2:27 PM

Aaron Bertrand [SQL Server MVP]

The last example here might help:
http://www.aspfaq.com/2427

Show quote

"Chris Strug" <hotm***@solace1884.com> wrote in message
news:OT7LApSiFHA.3544@TK2MSFTNGP15.phx.gbl...
> Hi,
>
> Probably a silly question but given the statement
>
> SELECT     B.Booking_Number, B.Job_Status, A.Address_1, A.Address_2,
> A.Address_3, A.Address_4, A.Address_Postcode
> FROM         dbo.tblAddress A RIGHT OUTER JOIN
>                      dbo.tblBooking B ON A.Booking_ID = B.Booking_ID
>
> where tblBooking (B) is the one side of the relation and table Address (A)
> is the many side, how do I number the address records for each Booking
> record?
>
> For example,
>
> Booking A has three address, hence they will be numbered 1, 2, 3
> Booking B has one address which will be numbered 1,
> Booking C has two addresses which will be numbered 1, 2
>
> I.e.
> Booking Ref    Booking_Data    Address_Data    Address_No
> A                    Info                     Add_Info2
> 1
> A                    More Info            Add_Info                     2
> A                    And more            Add_Info3                   3
> B                    data3                   Add_Data                    1
> C                    Data4                  Add_Data4                  1
> C                    Data5                  Add_Data5                  2
>
> I'm really not sure how to achieve this, if anyone could provide any
> pointers I'd be very grateful.
>
> Kind thanks
>
> Chris.
>