Help with query building.

[eluser]behnampmdg3[/eluser]
Hello;

I need to build a join query between 3 tables.

The end result shows a list of upcoming events. The events are stored in sincity_events table.

Each event may have celebrities attached to it (sincity_events_celebrities).

Each celebrity can be either "dj" or "celebrity' (type column).

I need to show list of events and all celebrities attending that event with this format:

Event Name: New Years Eve.
Celebrities: "Mike", "Luna".
DJs: "Jenniffer", "Tiest".

-------------------------------

Event Name: Christmas Party
Celebrities: "Mike", "Luna".
DJs: none
--------------------------------


Here are the 3 tables:

sincity_events_celebrities
sincity_events
sincity_celebrities

-------------------------------

Code:

CREATE TABLE IF NOT EXISTS `sincity_celebrities` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`about` text NOT NULL,
`facebook` varchar(255) NOT NULL,
`twitter` varchar(255) NOT NULL,
`website` varchar(255) NOT NULL,
`photo` varchar(255) NOT NULL,
`type` varchar(255) NOT NULL DEFAULT 'Celebrity',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;

Code:

CREATE TABLE IF NOT EXISTS `sincity_events` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`recurring` varchar(255) NOT NULL,
`title` varchar(255) NOT NULL,
`about` text NOT NULL,
`photo` varchar(255) NOT NULL,
`musicians` text NOT NULL,
`type` varchar(255) NOT NULL,
`date` date NOT NULL,
`display_date` varchar(255) NOT NULL,
`price` int(11) NOT NULL,
`recurring_day` varchar(255) NOT NULL,
`thumbnail_photo` varchar(255) NOT NULL,
`main_photo` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;

Code:

CREATE TABLE IF NOT EXISTS `sincity_events_celebrities` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`event_id` int(11) NOT NULL,
`celebrity_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=337 ;

Thank you.

[eluser]xtremer360[/eluser]
Did I miss something? Was there a question somewhere?

[eluser]cPage[/eluser]
Something like this

Code:

$query = $this->db->query('SELECT *
                FROM sincity_events_celebrities sec
                LEFT JOIN sincity_events se ON se.id = sec.event_id
                LEFT JOIN sincity_celebrities sc ON sc.id = sec.celebrity_id
                WHERE sec.event_id = se.id;');

[eluser]behnampmdg3[/eluser]
[quote author="xtremer360" date="1358740368"]Did I miss something? Was there a question somewhere?[/quote]Yes
Please show me how to write such query.
Thanks

[eluser]xtremer360[/eluser]
Sounded like you were telling us what the query should be and didn't ask any questions about how to accomplish said task..