dynamically select from 2 tables based on id

jmack159

hi there,

im working on a performance request system, and i have three tables that im working with right now. the performances table holds the info about the performance (date, times, status, etc.), and has two columns, org_type and org_id, which both hold an int value to tell me where the organization info is. there are two org types which have respective tables, because we collect different information based on the type of org that is registering. is there any way to select info from all three tables (dynamically selecting between the two org tables based on org_type, then selecting the proper org info based on org_id) in one query?

what im using:
mysql 5.0, MyISAM tables
php 5.2

thanks a lot!

--Josh

cgraz

Can you post the relevant table structure? ie.

Blocks(BlockID, Name )
Blocks_Position(BlockID, PageID)
Pages(PageID, PTitle, PBody)

For each of the three tables, do you want to return results only if they match up with another table, or also return results that have no match in their joining table?

jmack159

from phpmyadmin:

CREATE TABLE `performances` (
  `peform_id` int(3) NOT NULL auto_increment,
  `stime` int(11) NOT NULL,
  `etime` int(11) NOT NULL,
  `location` varchar(300) NOT NULL,
  `ref` varchar(500) NOT NULL,
  `desc` varchar(500) NOT NULL,
  `spec_req` varchar(500) NOT NULL,
  `name` varchar(50) NOT NULL,
  `position` varchar(30) NOT NULL,
  `phone` varchar(12) NOT NULL,
  `phone2` varchar(12) NOT NULL,
  `email` varchar(50) NOT NULL,
  `created` int(11) NOT NULL,
  `ip` varchar(15) NOT NULL,
  `status` int(2) NOT NULL,
  `org_type` int(2) NOT NULL,
  `org_id` int(4) NOT NULL,
  PRIMARY KEY  (`peform_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

CREATE TABLE `orgs` (
  `org_id` int(3) NOT NULL,
  `name` varchar(100) NOT NULL,
  `location` varchar(50) NOT NULL,
  `description` varchar(300) NOT NULL,
  `count` int(3) NOT NULL
  PRIMARY KEY  (`org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `units` (
  `unit_id` int(3) NOT NULL auto_increment,
  `unit_type` varchar(10) NOT NULL,
  `unit_num` int(8) NOT NULL,
  `location` varchar(50) NOT NULL,
  `council` varchar(50) NOT NULL,
  `count` varchar(3) NOT NULL,
  PRIMARY KEY  (`unit_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

so org_type in the performances table should tell me if the org for that performance is in the units table or the orgs table, then org_id in the performances table should give me the primary key for table indicated by org_type

i hope im explaining this correctly. also, if there is a better way to do this than what ive come up with, im all ears! thanks!

--Josh

Roger_Ramjet

Well you are always going to have a problem joining performance data to the organisation with a table layout like that. There is no simple way to differentiate which table the org_id relates to. You should never mix Foreign Keys like that in 1 column.

Since org_id in the performances table is part of a PK:FK pair it should only contain refs to the PK in the orgs table. So add another FK column for the unit_id from the units table and move the unit_ids into it. If you make both FK column default to 0 then it will be a simple matter to join performance data to the correct table since 0 will not match any of the PKs.

You will also be able to dispence with the org_type column if you wish as it will be redundant: org type will be indicated be the presence of a valid FK in the relevant column