Thursday, March 29, 2012

Combining two queries

These similar queries do much the same thing: the first one gets a list of ticket ID's that have been bought as 'standalone' tickets by a particular user, along with the total quantity they purchased. The second one also gets a list of ticket ID's along with the quantity purchased by that user, but the list of ID's is driven by tickets that appear in their basket as part of packages, instead of standalone tickets.

I hope that's clear; if not, maybe the SQL will make it clearer:

SELECT
[tblTickets].[id] AS TicketId,
SUM([tblBasket].[ticket_quantity]) AS SingleTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblTickets] ON [tblTickets].[id] = [tblBasket].[ticket_id]

WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @.userID AND ([tblOrders].[order_status]=@.purchasedOrder OR [tblOrders].[id]=@.currentSessionOrder))

GROUP BY [tblTickets].[id]

SELECT
[tblCombinations_Tickets].[ticket_id] AS cTicketId,
SUM([tblBasket].[ticket_quantity]*[tblCombinations_Tickets].[quantity]) AS PackageTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblBasket].[combination_id]

WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @.userID AND ([tblOrders].[order_status]=@.purchasedOrder OR [tblOrders].[id]=@.currentSessionOrder))

GROUP BY [tblCombinations_Tickets].[ticket_id]

I need to combine these. So that I get one result set with: ticketID, quantity bought as standalone, quantity bought as part of package.

I can't figure it out. I've tried inner joins, outer joins, left joins, right joins, nested subqueries and, briefly, banging on the screen. But every time, what happens is that I only get the rows where the ticket ID occurs in both queries. I need everything.

This has got to be laughably simple. But I'm stuck :( Can anyone help?first query goes here
UNION ALL
second query goes here|||first query goes here
UNION ALL
second query goes here

Fantastic :D I feel dim and enlightened at the same time :S Thankyou :)|||Fantastic :D I feel dim and enlightened at the same time :S Thankyou :)Rudy does have that affect :D

No comments:

Post a Comment