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

    Unanswered: Err.. GROUP BY, or something

    Right chaps - this should be an easy one, unfortunately I'm a bit of a SQL lightweight. I'm fine up to your basic JOIN but as soon as GROUP BY or UNION start getting involved, my brain just gives up.

    So: see attached schema wot I built. Hopefully it's completely self-explanatory. And hopefully it's well-designed although I'd appreciate any comments in that repect.

    To summarise briefly: Single tickets (see tblTickets) can be constructed by linking to parameters in any one of about 8 or so "sub" tables. So an entry in tblTickets may specify an event and a date, or an event and a concession and a stand. Combination tickets (see tblCombinations) can be constructed by combining Single ticket ID's.

    So, my problem:

    I want to select a list of Combination tickets, based on whether or not the Combination ticket's constituent Single tickets meet any of the parameters specified. A row for each single ticket, each with:
    tblCombinations.id
    tblCombinations.description
    tblCombinations.combination_open
    tblTickets.ticket_open
    tblEvents.event_open
    tblStands.stand_open
    tblAdmissionDates.date_open

    Right? Here's what I'm trying:

    Code:
    SELECT
    [tblCombinations].[id],
    [tblCombinations].[description],
    [tblCombinations].[combination_open],
    [tblTickets].[ticket_open],
    [tblEvents].[event_open],
    [tblStands].[stand_open],
    [tblAdmissionDates].[date_open]
    FROM
    [tblCombinations]
    LEFT JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblCombinations].[id]
    LEFT JOIN [tblTickets] ON [tblCombinations_Tickets].[ticket_id] = [tblTickets].[id]
    LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id]
    LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id]
    LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
    LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
    LEFT JOIN [tblBookingQuantities] ON [tblBookingQuantities].[id] = [tblTickets].[booking_quantity_id]
    LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
    LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id]
    WHERE 1=1
    AND [tblEvents].[id]=2
    AND [tblAdmissionDates].[id]=1
    GROUP BY
    [tblCombinations].[id],
    [tblCombinations].[description],
    [tblCombinations].[combination_open],
    [tblTickets].[ticket_open],
    [tblEvents].[event_open],
    [tblStands].[stand_open],
    [tblAdmissionDates].[date_open]
    ... which, unsurprisingly, isn't working.

    If I leave any of the fields that I want out of the GROUP BY clause, I get "Column [insert column name] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

    If I put them all in, I get "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

    As a note, the reason all of those tables are joined when they aren't in the SELECT or WHERE clauses, is that this is part of a web application, and so the (dynamically built) WHERE clause may contain parameters from any of those tables. Obviously if that's the root of the issue I'm having, then I can rewrite it to only join the tables that I need. But it's just easier to include them all. Unless that's also a huge performance hit... err... is it?

    Anyway, thanks for reading this, and a huge amount of potential gratitude if you can just explain to me what logical chasm I'm failing to bridge here
    Attached Thumbnails Attached Thumbnails schema.gif  

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    I dont understand, why are you grouping here,there is no aggregate function in ur query.
    Anyway,I have taken off [tblCombinations].[description] from group selection and selected that field after grouping.
    I hope that is the only text field in that query.

    Note: I have n't tested the query with tables.

    Code:
    SELECT  [tm].[id],
     [tblC].[description],
     [tm].[combination_open],
     [tm].[ticket_open],
     [tm].[event_open],
     [tm].[stand_open],
     [tm].[date_open]
    FROM
     (
     SELECT
      [tblCombinations].[id],
      [tblCombinations].[combination_open],
      [tblTickets].[ticket_open],
      [tblEvents].[event_open],
      [tblStands].[stand_open],
      [tblAdmissionDates].[date_open]
     FROM
      [tblCombinations]
      LEFT JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblCombinations].[id]
      LEFT JOIN [tblTickets] ON [tblCombinations_Tickets].[ticket_id] = [tblTickets].[id]
      LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id]
      LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id]
      LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
      LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
      LEFT JOIN [tblBookingQuantities] ON [tblBookingQuantities].[id] = [tblTickets].[booking_quantity_id]
      LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
      LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id]
     WHERE 1=1
      AND [tblEvents].[id]=2
      AND [tblAdmissionDates].[id]=1
     GROUP BY
      [tblCombinations].[id],
      [tblCombinations].[combination_open],
      [tblTickets].[ticket_open],
      [tblEvents].[event_open],
      [tblStands].[stand_open],
      [tblAdmissionDates].[date_open]
     ) 
    as [tm] join [tblCombinations] as tblC
     on [tm].[id]=tblC.[id]
    Last edited by mallier; 03-16-06 at 05:17.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Right - thanks very much for your help.... but that's not the only text field I need. Sorry :/

    Here's what I've got:
    Code:
    SELECT
    [tblC].[id] AS CombinationID,
    [tblC].[availability],
    [tblC].[description],
    [tblC].[price] AS combinationPrice,
    [tblC].[combination_open],
    [tblT].[TicketID] AS TicketID,
    [tblT].[price] AS ticketPrice,
    [tblT].[availability],
    [tblT].[ticket_open],
    [tblT].[quantity],
    [tblT].[event_name],
    [tblT].[event_open],
    [tblT].[stand_name],
    [tblT].[stand_open],
    [tblT].[admission_start_date],
    [tblT].[admission_end_date],
    [tblT].[date_open],
    [tblT].[booking_start_date],
    [tblT].[booking_end_date]
    FROM(
    	SELECT
    	[tblCombinations].[id],
    	[tblTickets].[id] As TicketID, [tblTickets].[price], [tblTickets].[availability], [tblTickets].[ticket_open],
    	[tblCombinations_Tickets].[quantity],
    	[tblEvents].[event_name],
    	[tblEvents].[event_open],
    	[tblStands].[stand_name],
    	[tblStands].[stand_open],
    	[tblAdmissionDates].[admission_start_date],
    	[tblAdmissionDates].[admission_end_date],
    	[tblAdmissionDates].[date_open],
    	[tblBookingDates].[booking_start_date],
    	[tblBookingDates].[booking_end_date]
    	
    	FROM [tblCombinations]
    	LEFT JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblCombinations].[id]
    	LEFT JOIN [tblTickets] ON [tblCombinations_Tickets].[ticket_id] = [tblTickets].[id]
    	LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id]
    	LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id]
    	LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
    	LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
    	LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
    	LEFT JOIN [tblBookingQuantities] AS [tblBookingMinQuantities] ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
    	LEFT JOIN [tblBookingQuantities] AS [tblBookingMaxQuantities] ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
    	LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id]
    	WHERE 1=1
    	AND [tblEvents].[id]=2
    	GROUP BY
    	[tblCombinations].[id],
    	[tblTickets].[id],
    	[tblTickets].[price], [tblTickets].[availability], [tblTickets].[ticket_open],
    	[tblCombinations_Tickets].[quantity],
    	[tblEvents].[event_name],
    	[tblEvents].[event_open],
    	[tblStands].[stand_name],
    	[tblStands].[stand_open],
    	[tblAdmissionDates].[admission_start_date],
    	[tblAdmissionDates].[admission_end_date],
    	[tblAdmissionDates].[date_open],
    	[tblBookingDates].[booking_start_date],
    	[tblBookingDates].[booking_end_date]
    ) as [tblT] JOIN [tblCombinations] as [tblC] on [tblT].[id]=[tblC].[id]
    I also need two text fields from tblTickets ("description" and "admin_description"), and I'm buggered if I can figure out how. I think I get the GROUP BY thing, it sort of... gets a new row for each distinct value in that column, right? But how do I get text fields in there? What's so special about them?

    Do I need to... embed another SELECT, inbetween the two existing ones? Something like that? Like, join tblTickets in twice; once to get the GROUP BY stuff, and again to get the text fields? Am I making any sense at all? Should I just go fetch a grown-up?

  4. #4
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    I ve included "describtion" and "admindescribtion".Still Im not sure abt ur grouping.
    Code:
    SELECT
    [tblC].[id] AS CombinationID,
    [tblC].[availability],
    [tblC].[description],
    [tblC].[price] AS combinationPrice,
    [tblC].[combination_open],
    [tblTkts].[description],
    [tblTkts].[admin_description],
    [tblT].[TicketID] AS TicketID,
    [tblT].[price] AS ticketPrice,
    [tblT].[availability],
    [tblT].[ticket_open],
    [tblT].[quantity],
    [tblT].[event_name],
    [tblT].[event_open],
    [tblT].[stand_name],
    [tblT].[stand_open],
    [tblT].[admission_start_date],
    [tblT].[admission_end_date],
    [tblT].[date_open],
    [tblT].[booking_start_date],
    [tblT].[booking_end_date]
    FROM(
     SELECT
     [tblCombinations].[id],
     [tblTickets].[id] As TicketID, [tblTickets].[price],
    [tblTickets].[availability], 
     [tblTickets].[ticket_open],
     [tblCombinations_Tickets].[quantity],
     [tblEvents].[event_name],
     [tblEvents].[event_open],
     [tblStands].[stand_name],
     [tblStands].[stand_open],
     [tblAdmissionDates].[admission_start_date],
     [tblAdmissionDates].[admission_end_date],
     [tblAdmissionDates].[date_open],
     [tblBookingDates].[booking_start_date],
     [tblBookingDates].[booking_end_date]
     
     FROM [tblCombinations]
     LEFT JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblCombinations].[id]
     LEFT JOIN [tblTickets] ON [tblCombinations_Tickets].[ticket_id] = [tblTickets].[id]
     LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id]
     LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id]
     LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
     LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
     LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
     LEFT JOIN [tblBookingQuantities] AS [tblBookingMinQuantities] ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
     LEFT JOIN [tblBookingQuantities] AS [tblBookingMaxQuantities] ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
     LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id]
     WHERE 1=1
     AND [tblEvents].[id]=2
     GROUP BY
     [tblCombinations].[id],
     [tblTickets].[id],
     [tblTickets].[price], [tblTickets].[availability], [tblTickets].[ticket_open],
     [tblCombinations_Tickets].[quantity],
     [tblEvents].[event_name],
     [tblEvents].[event_open],
     [tblStands].[stand_name],
     [tblStands].[stand_open],
     [tblAdmissionDates].[admission_start_date],
     [tblAdmissionDates].[admission_end_date],
     [tblAdmissionDates].[date_open],
     [tblBookingDates].[booking_start_date],
     [tblBookingDates].[booking_end_date]
    ) as [tblT] 
    JOIN [tblCombinations] as [tblC] on [tblT].[id]=[tblC].[id]
    LEFT JOIN [tblTickets] as tblTkts on  [tbIT].TicketID=[tblTkts].[id]
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Jan 2002
    Posts
    189
    Quote Originally Posted by mallier
    Still Im not sure abt ur grouping.
    Many thanks - I didn't release it would be that straightforward. And no, neither am I; I guess I'm still standing on the wrong side of that particular cognitive chasm. Ah well, I'm getting there

Posting Permissions

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