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
Code MySQL:
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

Code MySQL:
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 ;


Code MySQL:
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`)

Code MySQL:
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`) ;

Code MySQL:
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`);

Code MySQL:
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`);