CODEWITHSUNDEEP
sqlsql-server
Gumbo
Gumbo

Reputation: 201

SQL Join, display results from each table in a separate row

I need to be able to select each line from an invoice and display in separate rows. There are two types of lines, normal line and custom line.

To clarify, Each booking has one Cart booking that owns many Product bookings, each product booking can only have one invoice which can have both multiple normal(BokunProductInvoiceLine) lines and custom lines.

Here is the code:

Select 
CB.ConfirmationCode as 'Confirmation Code',
PB.ProductConfirmationCode as 'Product ConfirmationCode',
BPL.[Title] as 'Normal line title',
BPL.[CostGroupTitle] as 'Normal line title',
CL.Title as 'Custom line title'

From [dbo].[BokunProductBooking] PB
Join [dbo].[BokunCartBooking] CB
On Pb.ParentBookingId = CB.Id
Join [dbo].[BokunInvoice] BI
On Bi.BookingId = PB.Id  AND BI.Active = 1
left Join BokunCustomLine CL
On CL.InvoiceId = BI.Id
FULL JOIN BokunProductInvoiceLine BPL
On BPL.ProductInvoiceId = BI.Id

Here is the result I am getting with this query: enter image description here

And here is the result I am trying to get: enter image description here

Thanks :)

Upvotes: 1

Views: 267

Answers (1)

LukStorms
LukStorms

Reputation: 29667

Sounds like a job for "Union", but "Union All" will get you there faster.

Select 
CB.ConfirmationCode as [Confirmation Code],
PB.ProductConfirmationCode as [Product ConfirmationCode],
BPL.[Title],
BPL.[CostGroupTitle] as [Cost Group title],
cast(null as varchar(max)) as [Custom line title]
From [dbo].[BokunProductBooking] PB
Join [dbo].[BokunCartBooking] CB On Pb.ParentBookingId = CB.Id
Join [dbo].[BokunInvoice] BI On Bi.BookingId = PB.Id  AND BI.Active = 1
Join BokunProductInvoiceLine BPL On BPL.ProductInvoiceId = BI.Id

union all

Select 
CB.ConfirmationCode,
PB.ProductConfirmationCode,
NULL,
NULL,
CL.Title
From [dbo].[BokunProductBooking] PB
Join [dbo].[BokunCartBooking] CB On Pb.ParentBookingId = CB.Id
Join [dbo].[BokunInvoice] BI On Bi.BookingId = PB.Id  AND BI.Active = 1
Join BokunCustomLine CL On CL.InvoiceId = BI.Id

Upvotes: 1

Related Questions