[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.