CODEWITHSUNDEEP
mysqlsql
user1124378
user1124378

Reputation:

selecting row with many child rows

I am hoping I can make myself understood enough! I have the following SQL query

SELECT DATE_FORMAT(calendar_date,'%W %D, %M, %Y') AS calendar_date,calendar_entry_title,calendar_entry_teaser
        FROM calendar_month
        LEFT JOIN calendar_entry ON calendar_entry.calendar_id = calendar_month.calendar_id
        ORDER BY calendar_date

Here are the table detail I am dealing with.

CREATE TABLE IF NOT EXISTS `calendar_entry` (
  `calendar_entry_id` int(11) NOT NULL AUTO_INCREMENT,
  `calendar_id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `calendar_entry_title` varchar(250) NOT NULL,
  `calendar_entry_teaser` varchar(250) NOT NULL,
  `calendar_entry_text` text NOT NULL,
  PRIMARY KEY (`calendar_entry_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `calendar_entry`
--

INSERT INTO `calendar_entry` (`calendar_entry_id`, `calendar_id`, `school_id`, `calendar_entry_title`, `calendar_entry_teaser`, `calendar_entry_text`) VALUES
(1, 1, 1, 'School Event 1', 'School event information 1', 'This would be the full body of the text that would show on the full page for this given entry'),
(2, 1, 1, 'School Event 2', 'School event information 2', 'This would be the full body of the text that would show on the full page for this given entry');



CREATE TABLE IF NOT EXISTS `calendar_month` (
  `calendar_id` int(11) NOT NULL AUTO_INCREMENT,
  `school_id` int(11) NOT NULL,
  `calendar_date` date NOT NULL,
  PRIMARY KEY (`calendar_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `calendar_month`
--

INSERT INTO `calendar_month` (`calendar_id`, `school_id`, `calendar_date`) VALUES
(1, 1, '2012-08-11'),
(2, 1, '2012-08-12');

The problem I have is, there are only 2 rows in the calendar_month table. One of these rows has 2 rows related to it in the month_entry table. When I run the query that I have it will display 3 rows. what I need it to do is only show 2 rows, the month that has two rows I need to be displayed as one row. Can this be done with how I have set it up?

Thanks

result -

Saturday 11th, August, 2012     School Event 1  School event information 1
Saturday 11th, August, 2012     School Event 2  School event information 2
Sunday 12th, August, 2012   NULL    NULL

What I actually want -

Saturday 11th, August, 2012     School Event 1  School event information 1 School Event 2   School event information 2
Sunday 12th, August, 2012   NULL    NULL

Upvotes: 1

Views: 191

Answers (2)

pixeline
pixeline

Reputation: 17984

You can't get that kind of result in mysql only, or using 2 subquery for each row, which will sooner or later crash your server. Rather, use php to sort through the result and store in which calendar_entry_title you are. example:

$query =" SELECT DATE_FORMAT(calendar_date,'%W %D, %M, %Y') AS calendar_date,
calendar_entry_title,
calendar_entry_teaser
        FROM calendar_month
        LEFT JOIN calendar_entry ON calendar_entry.calendar_id = calendar_month.calendar_id
        ORDER BY calendar_date, calendar_entry_title";

$result = mysql_query($query);
$events = array();
foreach($result as $r){
     $events[$r['calendar_date']][] = $r;
}
echo '<pre>';
print_r($events);
echo '</pre>';

Upvotes: 0

mrok
mrok

Reputation: 2710

did you try

SELECT DATE_FORMAT(calendar_date,'%W %D, %M, %Y') AS calendar_date, TMP.var1
FROM calendar_month
LEFT JOIN 
    (SELECT GROUP_CONCAT(calendar_entry_title, ' ',calendar_entry_teaser) AS var1, calendar_id
         FROM calendar_entry
     GROUP BY calendar_id) AS TMP ON TMP.calendar_id = calendar_month.calendar_id
ORDER BY calendar_date

Upvotes: 0

Related Questions