I have two tables:
Code: Select all
create table func(id int(3) AUTO_INCREMENT, title varchar(150), description mediumtext, sector int(1), PRIMARY KEY(id));
create table funcsectors(heading varchar(100), sid int(2), blurb mediumtext, primary key(sid));The second table is the list of job sectors (sector title, sector description, sector[id no])
The list I wish to present in PHP is as follows. I have the code in place which will iterate through the resultant table and only print sector information the first time that a new sector is encountered.
SECTOR TITLE
SECTOR BLURB
JOB TITLE - JOB DESCRIPTION
JOB TITLE - JOB DESCRIPTION
SECTOR TITLE
SECTOR BLUR......
Simple enough if it wasn't for the fact that not all sectors have BLURB and not all sectors have jobs so in this case it would be..
SECTOR TITLE
SECTOR BLURB
SECTOR TITLE
SECTOR BLURB
JOB TITLE - JOB DESCRIPTION
JOB TITLE - JOB DESCRIPTION
SECTOR TITLE
JOB TITLE - JOB DESCRIPTION
The following code:
Code: Select all
SELECT DISTINCT heading, id, title, description, funcsectors.sid AS sid, blurb FROM funcsectors,func WHERE funcsectors.sid = func.sector ORDER BY funcsectors.sid, id;I figure I need a JOIN statement which will somehow include this information whether or not there are jobs in that sector.
I've studied and played around with the example join code but had no luck.
Please could someone assist me with this query.