Hello
I'v got the following tables
CREATE TABLE `aa` (
`aaid` tinyint(4) NOT NULL auto_increment,
`test` varchar(255) NOT NULL default '',
PRIMARY KEY (`aaid`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;
INSERT INTO `aa` (`aaid`, `test`) VALUES (1, 'row1');
INSERT INTO `aa` (`aaid`, `test`) VALUES (2, 'row2');
INSERT INTO `aa` (`aaid`, `test`) VALUES (3, 'row3');
CREATE TABLE `bb` (
`bbid` tinyint(3) unsigned NOT NULL auto_increment,
`prop` varchar(255) NOT NULL default '',
`value` enum('Y','N') NOT NULL default 'Y',
`linkto` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`bbid`)
) TYPE=MyISAM AUTO_INCREMENT=7 ;
INSERT INTO `bb` (`bbid`, `prop`, `value`, `linkto`) VALUES (1,
'prop1','Y', 1);
INSERT INTO `bb` (`bbid`, `prop`, `value`, `linkto`) VALUES (2,
'prop_cool','N', 2);
INSERT INTO `bb` (`bbid`, `prop`, `value`, `linkto`) VALUES (3,
'lalala','Y',1);
INSERT INTO `bb` (`bbid`, `prop`, `value`, `linkto`) VALUES (4,
'wolle','Y', 3);
INSERT INTO `bb` (`bbid`, `prop`, `value`, `linkto`) VALUES (5,
'rose', 'N',3);
INSERT INTO `bb` (`bbid`, `prop`, `value`, `linkto`) VALUES (6,
'kaufe','N', 3);
I want to do the following
SELECT * FROM aa
LEFT JOIN bb on (aa.aaid = bb.linkto)...
this works fine, but i want to use the values of bb.prop as new
fields!
--------------------------------------------------------------
||aaid | test | prop1 | lalala || ... wolle | rose | kaufe |
---------------------------------
|| 1 | row1 | Y | Y ||
----------------------------------
.......
---------------------------------
as example aa.lalala shall become a new field of the resultset
(lala is the value of a row from the bb table (field prop)
How could I realise this?
I hope you could understand what I mean... ;)
The point is to use a "value of a field" of one table, as a field of
the resultset, having the name of the value of the first table, and
having as value the value of another field in the first table (uff...)
Thanks in advance
Marc