Assistance requested with a query

Databases
1

I am finding this to be quite a complicated query.

I am trying to get the data for each event, from the several tables that it is stored in.

there may or may not be an image, so I made a LEFT OUTER join.

I’ll post the query and then the create table statements and would appreciate your help.

Also, should foreign keys go from grandchild to child or straight to the parents? eg, event_images linking to ‘events_data’ OR should it link directly, to ‘events’

bazz


select
            base_events.event_id
          , base_events.event_name_in_home_language
          , base_events.live_from
          , base_events.live_to      
          , event_data.file_data as file_data
          , pd.pricing_data as pricing_data
          , c.symbol_left
	  , c.symbol_right
	  , ei.image
       FROM events as base_events
         
   INNER     
       JOIN ( SELECT id
                   , event_id
	           , GROUP_CONCAT(
                     CONCAT_WS( ','
	                      , ed.file_heading
                              , ed.text_block_sequence
                              , ed.text_or_list
			      , ed.file_text
			      , ei.image
			     
                              ) order by ed.text_block_sequence 
                        SEPARATOR ';' ) AS file_data
                FROM events_data eda
	left outer
		join event_images as imgs
		on imgs.event_id = eda.event_id
		) as ed
         ON ed.event_id = base_events.event_id
		
   
   INNER
       JOIN ( select eh.event_id
                   , GROUP_CONCAT(
		     CONCAT_WS( ','
		              , eh.day_of_week
			      , eh.opening
			      , eh.closing
			      , ep.price
			      , ep.text_price
			      , ep.age_group
			      
		              )
		        SEPARATOR ';' ) as pricing_data
		FROM events_hours as eh
	      INNER
	        JOIN events_prices as ep
		  on ep.event_id = eh.event_id
		 and ep.hours_id = eh.hours_id
		 GROUP
		   BY ep.event_id
            ) as pd
         ON pd.event_id = base_events.event_id	    
   INNER
       JOIN business_currencies as bc
         ON bc.business_id = base_events.business_id
   INNER
       JOIN currency as c
         on c.currency = bc.currency
    
   #where base_events.business_id = 123



CREATE TABLE IF NOT EXISTS `events` (
  `event_id` int(11) NOT NULL auto_increment,
  `business_id` int(11) NOT NULL,
  `content_category` varchar(99) collate utf8_unicode_ci NOT NULL,
  `live_from` date NOT NULL,
  `live_to` date NOT NULL,
  `event_name_in_home_language` varchar(32) collate utf8_unicode_ci NOT NULL,
  `last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `last_updated_by` varchar(99) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`event_id`),
  UNIQUE KEY `business_file_name_fk` (`business_id`,`event_name_in_home_language`),
  KEY `fnhl_ix` (`event_name_in_home_language`),
  KEY `business_id` (`business_id`),
  KEY `files_categories_fk` (`business_id`,`content_category`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;




CREATE TABLE IF NOT EXISTS `events_data` (
  `id` bigint(11) NOT NULL auto_increment,
  `event_id` int(11) NOT NULL,
  `language_abbr_this_file` varchar(3) collate utf8_unicode_ci NOT NULL default 'ENG',
  `displayed_event_name` varchar(99) collate utf8_unicode_ci default NULL,
  `text_block_sequence` tinyint(2) unsigned zerofill NOT NULL,
  `text_or_list` varchar(4) collate utf8_unicode_ci NOT NULL default 'text',
  `file_heading` varchar(255) collate utf8_unicode_ci default NULL,
  `file_text` text collate utf8_unicode_ci NOT NULL,
  `last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `last_updated_by` varchar(32) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`event_id`,`text_block_sequence`),
  KEY `lang_ix` (`language_abbr_this_file`),
  KEY `file_ix` (`event_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=122 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `events_data`
--
ALTER TABLE `events_data`
  ADD CONSTRAINT `event_data_event_fk` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`)



CREATE TABLE IF NOT EXISTS `events_hours` (
  `hours_id` bigint(20) NOT NULL auto_increment,
  `event_id` int(11) NOT NULL,
  `day_of_week` varchar(99) collate utf8_unicode_ci default NULL,
  `opening` time default '00:00:00',
  `closing` time default '00:00:00',
  PRIMARY KEY  (`hours_id`),
  UNIQUE KEY `event_id` (`event_id`,`day_of_week`,`opening`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=162 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `events_hours`
--
ALTER TABLE `events_hours`
  ADD CONSTRAINT `event_hours_events_fk` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`) ;



CREATE TABLE IF NOT EXISTS `events_prices` (
  `event_id` int(20) NOT NULL,
  `hours_id` bigint(11) NOT NULL,
  `price` varchar(64) collate utf8_unicode_ci default NULL,
  `text_price` varchar(99) collate utf8_unicode_ci default NULL,
  `age_group` varchar(99) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`event_id`,`hours_id`,`age_group`),
  KEY `event_prices_hours_fk` (`hours_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `events_prices`
--
ALTER TABLE `events_prices`
  ADD CONSTRAINT `event_prices_hours_fk` FOREIGN KEY (`hours_id`) REFERENCES `events_hours` (`hours_id`);




CREATE TABLE IF NOT EXISTS `event_images` (
  `event_id` int(11) NOT NULL,
  `file_text_id` bigint(20) NOT NULL,
  `image` varchar(99) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`file_text_id`,`image`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `event_images`
--
ALTER TABLE `event_images`
  ADD CONSTRAINT `image_file_data_fk` FOREIGN KEY (`file_text_id`) REFERENCES `events_data` (`id`);
2

to begin with, try adding a GROUP BY clause to your subqueries which use GROUP_CONCAT

GROUP_CONCAT is an aggregate function and therefore requires a GROUP BY clause if there are non-aggregate expressions in the SELECT clause

if that doesn’t fix your problem, which you neglected to describe, then perhaps you could explain what the query isn’t doing correctly that it should be doing

3

Thanks rudy.

The query returns all possible results, correctly, all on the one page.

I want to return 5, per page.

when I apply limit 0, 5, the number of results returned is 3 in total instead of 5. :confused:

I have tried grouping on various columns but, I need help to know which group by’s are wrong or what else, I should be looking at.
here’s the query, with the limit clause hashed out and it returns all 7 results. if I use limit, it only returns 3 results.

select
        base_events.event_id
      , base_events.event_name_in_home_language
      , base_events.live_from 
      , base_events.live_to
      , ed.event_data as events_data
      , hd.hours_data as hours_data
      , pd.pricing_data as pricing_data
      , base_currency.symbol_left
      , base_currency.symbol_right
    
     FROM businesses AS base
 INNER
     JOIN events AS base_events
       ON base_events.business_id = base.id
       
 
 # get the text blocks and images
 left outer
     JOIN ( select ed.event_id
                 , ed.id
              , GROUP_CONCAT(
                CONCAT_WS(','
                 , ed.id
                     , ed.text_block_sequence
             , ed.displayed_event_name
             , ed.text_or_list
             , ed.file_text
             , ei.image
                        ) order by ed.file_heading, ed.text_block_sequence asc
                 SEPARATOR ';' ) AS event_data
              from events_data AS ed
       left outer
          join event_images AS ei
          on ei.event_id = ed.event_id
         AND ei.file_text_id = ed.id
         group by ed.event_id
    ) as ed
    on ed.event_id = base_events.event_id
         
 
 # get the hours
 left outer
     JOIN ( select eh.event_id
                 , eh.hours_id
              , GROUP_CONCAT(
                CONCAT_WS(','
                     , eh.day_of_week
             , TIME_FORMAT(eh.opening, '%l:%i %p') 
             , TIME_FORMAT(eh.closing, '%l:%i %p') 
                        ) order by eh.day_of_week asc
                 SEPARATOR ';' ) AS hours_data
              from events_hours AS eh
          group by eh.hours_id 
      ) as hd
    on hd.event_id = base_events.event_id
         
   #get the pricing
   left outer
     JOIN ( select ep.event_id
                 , ep.hours_id
              , GROUP_CONCAT(
                CONCAT_WS(','
                     , ep.age_group
             , ep.price
             , ep.text_price 
             
                        ) order by ep.age_group asc
                 SEPARATOR ';' ) AS pricing_data
              from events_prices AS ep
          group by ep.event_id
      ) as pd
    on pd.event_id = base_events.event_id
   AND pd.hours_id = hd.hours_id
   
   #get the currency
 INNER       
     JOIN business_currencies AS base_bc
       ON base_bc.business_id = base.id
 INNER
     JOIN currency AS base_currency
       on base_currency.currency = base_bc.currency     
    WHERE base.id = 123
      #group by events_data
          ORDER
        BY live_from desc
         # limit 0,5
4

got it :slight_smile:

well I think I have.

I added a group by to the main query and instead of couting the number of event_ids returned, I reverted to SQL_CALC_FOUND_ROWS

Still to test it further.


select SQL_CALC_FOUND_ROWS 
        base_events.event_id
      , base_events.event_name_in_home_language
      , base_events.live_from 
      , base_events.live_to
      , ed.event_data as events_data
      , hd.hours_data as hours_data
      , pd.pricing_data as pricing_data
      , base_currency.symbol_left
      , base_currency.symbol_right
    
     FROM businesses AS base
 INNER
     JOIN events AS base_events
       ON base_events.business_id = base.id
       
 
 # get the text blocks and images
 left outer
     JOIN ( select ed.event_id
                 , ed.id
		# , ed.displayed_event_name
              , GROUP_CONCAT(
                CONCAT_WS(','
		         , ed.id
	                 , ed.text_block_sequence
			 , ed.displayed_event_name
			 , ed.text_or_list
			 , ed.file_text
			 , ei.image
                        ) order by ed.file_heading, ed.text_block_sequence asc
                 SEPARATOR ';' ) AS event_data
              from events_data AS ed
	   left outer
	      join event_images AS ei
	      on ei.event_id = ed.event_id
	     AND ei.file_text_id = ed.id
	     group by ed.id
    ) as ed
    on ed.event_id = base_events.event_id
    
	     
 
 # get the hours
 left outer
     JOIN ( select eh.event_id
                 , eh.hours_id
              , GROUP_CONCAT(
                CONCAT_WS(','
	                 , eh.day_of_week
			 , TIME_FORMAT(eh.opening, '%l:%i %p') 
			 , TIME_FORMAT(eh.closing, '%l:%i %p') 
                        ) order by eh.day_of_week asc
                 SEPARATOR ';' ) AS hours_data
              from events_hours AS eh
	      group by eh.hours_id 
	  ) as hd
    on hd.event_id = base_events.event_id
	     
   #get the pricing
   left outer
     JOIN ( select ep.event_id
                 , ep.hours_id
              , GROUP_CONCAT(
                CONCAT_WS(','
	                 , ep.age_group
			 , ep.price
			 , ep.text_price 
			 
                        ) order by ep.age_group asc
                 SEPARATOR ';' ) AS pricing_data
              from events_prices AS ep
	      group by ep.event_id
	  ) as pd
    on pd.event_id = base_events.event_id
   AND pd.hours_id = hd.hours_id
   
   #get the currency
 INNER	   
     JOIN business_currencies AS base_bc
       ON base_bc.business_id = base.id
 INNER
     JOIN currency AS base_currency
       on base_currency.currency = base_bc.currency	 
    WHERE base.id = 123
      group by base_events.event_id
          ORDER
        BY live_from desc
          limit 0,5