MySQL Forums
Forum List  »  General

How to add column to view for each record in JOIN table?
Posted by: Tobias Krais
Date: July 16, 2011 04:18PM

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

Options: ReplyQuote


Subject
Written By
Posted
How to add column to view for each record in JOIN table?
July 16, 2011 04:18PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.