Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: 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:

    Code:
    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]
    Code:
    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    first query goes here
    UNION ALL
    second query goes here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Quote Originally Posted by r937
    Code:
    first query goes here
    UNION ALL
    second query goes here
    Fantastic I feel dim and enlightened at the same time Thankyou

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Spudhead
    Fantastic I feel dim and enlightened at the same time Thankyou
    Rudy does have that affect
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •