Mike Blezien <[EMAIL PROTECTED]> wrote on 10/04/2005 10:08:24 AM:
> Hello,
>
>
> [EMAIL PROTECTED] wrote:
> > 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.
> >>
>
> >
> >
> > 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
> >
>
>
> the `school_job` table should tie them together, the jobid and schoolid
would
> tie them together in the `job_posts` table, the schoolid(in the
`school_job`
> table), would be the same value as the accountid in the `school_access`
and
> `school_region` tables, just assigned a different column name for
> other reasons.
>
> by the way, this is MySQL Ver., 3.23.58 which I can not upgrade either
>
> Hope this helps. :)
It certainly does
SELECT
jp.*
, sj.schoolid
, sr.country
FROM job_posts jp
INNER JOIN school_job sj
ON sj.jobid = jp.jobid
INNER JOIN school_access sa
ON sa.accountid = sj.schoolid
LEFT JOIN school_region sr
ON sj.schoolid = sr.accountid
WHERE sa.status = 'active';
That will list every job in any school (whether it has a region or not)
where the status is 'active'. Does this answer your needs?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
