How to add column to view for each record in JOIN table?
Hi together,
I'm not a MySQL hero, thus I need some help. Here are my three tables:
-----%<-----
CREATE TABLE config` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(50) collate utf8_general_ci default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1;
CREATE TABLE property` (
`id` int(10) unsigned NOT NULL auto_increment,
`objekttitel` varchar(255) collate utf8_general_ci default NULL,
`objektnummer_intern` varchar(255) collate utf8_general_ci default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1;
CREATE TABLE exports` (
`id` int(10) unsigned NOT NULL auto_increment,
`property_id` int(10) unsigned NOT NULL,
`export_config_id` int(10) unsigned NOT NULL,
`export_timestamp` timestamp default 0,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1;
-----%<-----
Where table exports is an n:n table for property:config. If a record in table exports is available, it means this property will be exported to the given config.
What I want to do is a JOIN like this:
-----%<-----
SELECT property.id, property.objektnummer_intern, property.objekttitel FROM property LEFT JOIN exports ON (property.id = exports.property_id)
-----%<-----
And now I want to add one column for each row in table config. This column should show 'online' if a record for the property and the config is available. If none, it should show offline.
After googleing around for some hours I found this forum and hope to find help :-). Is it possible, to create this in one view?
Greetings,
Tobias