Description:
mysql> desc customfieldvalue;
+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| ValueID | int(10) unsigned | | PRI | NULL | auto_increment |
| CustomFieldID | int(11) unsigned | YES | MUL | NULL | |
| Value | varchar(25) | YES | | NULL | |
+---------------+------------------+------+-----+---------+----------------+
3 rows in set (0.10 sec)
mysql> SELECT cf.customfieldid, cf.name, cf.fieldtype,cf.recordType,cfv.valueid,cfv.value ,cfm.valueID as selected from customfield cf,customfieldvalue cfv left outer join customfieldmultiple cfm on ( cfv.customfieldid = cfm.customfieldid and cfv.valueid = cfm.valueid and cfm.recordid = 1 ),cvtable where cf.customfieldid = cfv.customfieldid and cf.recordtype = cvtable.tableid and cvtable.name = 'Entity' union SELECT cf.customfieldid, cf.name, cf.fieldtype,cf.recordType,null ,cfs.value ,null as seleted from customfield cf,customfieldscalar cfs ,cvtable where cf.customfieldid = cfs.customfieldid and cf.recordtype = cvtable.tableid and cvtable.name = 'Entity' and cfs.recordid = 1 order by cfv.value;
ERROR 1054: Unknown column 'cfv.value' in 'ORDER BY'
mysql> SELECT cf.customfieldid, cf.name, cf.fieldtype,cf.recordType,cfv.valueid,cfv.value ,cfm.valueID as selected from customfield cf,customfieldvalue cfv left outer join customfieldmultiple cfm on ( cfv.customfieldid = cfm.customfieldid and cfv.valueid = cfm.valueid and cfm.recordid = 1 ),cvtable where cf.customfieldid = cfv.customfieldid and cf.recordtype = cvtable.tableid and cvtable.name = 'Entity' union SELECT cf.customfieldid, cf.name, cf.fieldtype,cf.recordType,null ,cfs.value ,null as seleted from customfield cf,customfieldscalar cfs ,cvtable where cf.customfieldid = cfs.customfieldid and cf.recordtype = cvtable.tableid and cvtable.name = 'Entity' and cfs.recordid = 1 order by cfv.Value;
ERROR 1054: Unknown column 'cfv.Value' in 'ORDER BY'
mysql> SELECT cf.customfieldid, cf.name, cf.fieldtype,cf.recordType,cfv.valueid,cfv.value ,cfm.valueID as selected from customfield cf,customfieldvalue cfv left outer join customfieldmultiple cfm on ( cfv.customfieldid = cfm.customfieldid and cfv.valueid = cfm.valueid and cfm.recordid = 1 ),cvtable where cf.customfieldid = cfv.customfieldid and cf.recordtype = cvtable.tableid and cvtable.name = 'Entity' union SELECT cf.customfieldid, cf.name, cf.fieldtype,cf.recordType,null ,cfs.value ,null as seleted from customfield cf,customfieldscalar cfs ,cvtable where cf.customfieldid = cfs.customfieldid and cf.recordtype = cvtable.tableid and cvtable.name = 'Entity' and cfs.recordid = 1;
+---------------+----------+-----------+------------+---------+----------+----------+
| customfieldid | name | fieldtype | recordType | valueid | value | selected |
+---------------+----------+-----------+------------+---------+----------+----------+
| 1 | Type | MULTIPLE | 1 | 1 | Customer | NULL |
| 1 | Type | MULTIPLE | 1 | 2 | Vendor | NULL |
| 1 | Type | MULTIPLE | 1 | 3 | Reseller | NULL |
| 3 | Status | MULTIPLE | 1 | 4 | Active | NULL |
| 3 | Status | MULTIPLE | 1 | 5 | Inactive | NULL |
| 2 | Industry | SCALAR | 1 | 0 | | NULL |
+---------------+----------+-----------+------------+---------+----------+----------+
6 rows in set (0.09 sec)
How to repeat:
create a table with the structure above, perform the query listed below it.
