Hello,
I am trying to put together a single query from across 4 tables and not having a
problem getting the results needed. table structure:
######################################################
CREATE TABLE IF NOT EXISTS job_posts (
jobid int(6) unsigned NOT NULL auto_increment,
job_vacancy mediumtext NOT NULL,
job_information mediumtext NOT NULL,
job_procedure mediumtext NOT NULL,
job_posttime varchar(10) NOT NULL default '',
PRIMARY KEY (jobid)
) TYPE=InnoDB;
CREATE TABLE IF NOT EXISTS school_job (
jobid int(6) unsigned NOT NULL default '0',
schoolid int(6) unsigned NOT NULL default '0',
KEY jobid (jobid,schoolid)
) TYPE=InnoDB;
CREATE TABLE IF NOT EXISTS school_region (
accountid int(6) unsigned NOT NULL default '0', # equal as schoolid
region_id tinyint(2) unsigned NOT NULL default '0',
country char(2) NOT NULL default '',
PRIMARY KEY (accountid),
KEY region_id (region_id)
) TYPE=InnoDB;
CREATE TABLE IF NOT EXISTS school_access (
accountid int(6) unsigned NOT NULL auto_increment, # equal to schoolid
groupid enum('sc_school','sc_user','sc_admin') NOT NULL default 'sc_school',
username varchar(40) binary NOT NULL default '',
password varchar(20) NOT NULL default '',
status enum('active','pending','expired','pending_paid') NOT NULL default
'pending',
acctdays smallint(4) NOT NULL default '0',
regdate date NOT NULL default '0000-00-00',
PRIMARY KEY (accountid),
KEY username (username,status,acctdays)
) TYPE=InnoDB;
##########################################################3
what we need to obtain in the query are the following.
all data from `job_posts', schoolid from `school_job`, country from
`school_region` where `school_access` status = 'active'
the `school_job` and `school_access` tables are also used to tie(join) too a
`school_profile` table(all personal data)
any help with optimizing a single query would be appreciated.
TIA,
--
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com/
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
