MySQL Forums
Forum List  »  Newbie

joined query help needed
Posted by: Kirby Bakken
Date: March 16, 2010 10:23AM

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.

Options: ReplyQuote


Subject
Written By
Posted
joined query help needed
March 16, 2010 10:23AM


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.