-- is this used???
CREATE view DBO.Treservationaddressxtabview as

SELECT R.ReferenceNumber,R.GuestNumber, RAX1.AddressNumber, RAX2.AddressNumber AS CompanyNumber, RAX3.AddressNumber AS AgencyNumber, RAX4.AddressNumber AS AgentNumber FROM Reservations R WITH (NOLOCK)
LEFT OUTER JOIN ReservationAddressXTab RAX1 WITH (NOLOCK) ON R.ReferenceNumber=RAX1.ReferenceNumber AND R.GuestNumber=RAX1.GuestNumber AND RAX1.AddressTypeNumber=1
LEFT OUTER JOIN ReservationAddressXTab RAX2 WITH (NOLOCK) ON R.ReferenceNumber=RAX2.ReferenceNumber AND R.GuestNumber=RAX2.GuestNumber AND RAX2.AddressTypeNumber=2
LEFT OUTER JOIN ReservationAddressXTab RAX3 WITH (NOLOCK) ON R.ReferenceNumber=RAX3.ReferenceNumber AND R.GuestNumber=RAX3.GuestNumber AND RAX3.AddressTypeNumber=3
LEFT OUTER JOIN ReservationAddressXTab RAX4 WITH (NOLOCK) ON R.ReferenceNumber=RAX4.ReferenceNumber AND R.GuestNumber=RAX4.GuestNumber AND RAX4.AddressTypeNumber=4

/*
select distinct rax1.referencenumber, rax1.guestnumber, --ra1.addressnumber AddressNumber,
(select ra.addressnumber from reservationaddress ra (nolock) inner join reservationaddressxtab rax(nolock) on ra.addressnumber = rax.addressnumber and rax.referencenumber = rax1.referencenumber and rax.guestnumber = rax1.guestnumber and addresstype = 1) AddressNumber,
(select ra.addressnumber from reservationaddress ra (nolock) inner join reservationaddressxtab rax(nolock) on ra.addressnumber = rax.addressnumber and rax.referencenumber = rax1.referencenumber and rax.guestnumber = rax1.guestnumber and addresstype = 2) CompanyNumber,
(select ra.addressnumber from reservationaddress ra (nolock) inner join reservationaddressxtab rax(nolock) on ra.addressnumber = rax.addressnumber and rax.referencenumber = rax1.referencenumber and rax.guestnumber = rax1.guestnumber and addresstype = 3) AgencyNumber,
(select ra.addressnumber from reservationaddress ra (nolock) inner join reservationaddressxtab rax(nolock) on ra.addressnumber = rax.addressnumber and rax.referencenumber = rax1.referencenumber and rax.guestnumber = rax1.guestnumber and addresstype = 4) AgentNumber
from reservationaddressxtab rax1(nolock) 
--  inner join reservationaddress ra1(nolock)
--    on ra1.addressnumber = rax1.addressnumber and ra1.addresstype = 1

*/

