Mike Blezien <[EMAIL PROTECTED]> wrote on 10/04/2005 08:34:56 AM:
> 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/
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
I can't see (based on your column names) how to associate one row from
job_posts to any other row(s) in either school_region or school_access.
Did you leave out a table definition (or two)? I can go through school_job
to get at the schoolid but I still cannot relate to either of those
tables. I need some way of matching either jobid to accountid or schoolid
to accountid.
I have to have some way of tying those four tables together in order to
build your query.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
