joined query help needed
I've got three tables, person_per, person_custom, and person_custom_master.
The idea is that person_per has unique ID values in the per_ID column, as does person_custom. The person_custom table then has 'n' columns, c2, c3, c4.... cn. The 'name' of the c2, c3, etc. columns is contained in the person_custom_master table. That table has a custom_Field column, and a custom_Name column. The values of the custom_field column are c2, c3, c4, etc. I need to do a query of the values in person_custom, using the name of those c2, c3, etc. fields from person_custom_master.
So, for example, if the person_custom_master table had a row where custom_Field was equal to 'c3', and the custom_Name was 'Birth Date'. I can do a query like this:
SELECT per_Name FROM person_per LEFT JOIN person_custom ON person_per.per_ID=person_custom.per_ID WHERE c3='2010-03-10'. But I'd like to do a query where I said Birth_Date='2010-03-10'. I don't know how to associate the table value of person_custom_master for Birth Date with the 'c3' name in the person_custom table.
Here's the layout of person_custom_master:
custom_Order custom_Field custom_Name custom_Special custom_Side custom_FieldSec type_ID
3 c4 Year Graduated NULL left 1 2
1 c2 Date Deceased NULL left 1 2
2 c3 Baptized NULL left 1 2
4 c5 Maiden Name NULL right 1 3
Here's a few rows of person_custom:
per_ID c2 c3 c4 c5
1284 NULL NULL NULL NULL
1285 NULL NULL NULL NULL
658 2010-02-13 NULL NULL NULL
Then the person_per row would have a per_ID column, and ID's from 1 to how many person's..
Here are the table create commands:
CREATE TABLE `person_custom` (
`per_ID` mediumint(9) NOT NULL default '0',
PRIMARY KEY (`per_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `person_custom_master` (
`custom_Order` smallint(6) NOT NULL default '0',
`custom_Field` varchar(5) NOT NULL default '',
`custom_Name` varchar(40) NOT NULL default '',
`custom_Special` mediumint(8) unsigned default NULL,
`custom_Side` enum('left','right') NOT NULL default 'left',
`custom_FieldSec` tinyint(4) NOT NULL,
`type_ID` tinyint(4) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
and for person_per:
CREATE TABLE `person_per` (
`per_ID` mediumint(9) unsigned NOT NULL auto_increment,
`per_Title` varchar(50) default NULL,
`per_FirstName` varchar(50) default NULL,
`per_MiddleName` varchar(50) default NULL,
`per_LastName` varchar(50) default NULL,
`per_Suffix` varchar(50) default NULL,
etc....
The contents of each row are dynamically created, but I think the 'dump' of a few rows of the two custom tables will help.
Thank you.