[EMAIL PROTECTED] wrote:
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?
I think this should do it. many thanks for the help :)
--
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]
