I think I need a JOINt

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
leenoble_uk
Forum Contributor
Posts: 108
Joined: Fri May 03, 2002 10:33 am
Location: Cheshire
Contact:

I think I need a JOINt

Post by leenoble_uk »

I hope someone can help me concoct what I think must be a JOIN statement in MySQL.
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));
Basically, the first table contains jobs (job title, job description AND job sector[id no])
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;
misses out those job sectors which don't have any jobs and fails to output both their sector title and blurb.
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.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

POSTGRES has LEFT OUTER JOIN which you could use as follows

Code: Select all

SELECT DISTINCT heading, id, title, description, tablea.sid AS sid, tablea.blurb FROM func LEFT OUTER JOIN (SELECT * FROM funcsectors) AS tablea ON tablea.sid = func.sector  ORDER BY func.sid, id;
or at least something like that. Unfortunately I cannot test it at the moment. You also need to check MYSQL support for LEFT OUTER JOIN but should give you a pointer
leenoble_uk
Forum Contributor
Posts: 108
Joined: Fri May 03, 2002 10:33 am
Location: Cheshire
Contact:

Post by leenoble_uk »

No, sorry, I couldn't persuade that to work.
At the moment I'm having to use multiple SELECT queries in the PHP as follows:

Code: Select all

<?php
$sql = "SELECT * FROM funcsectors ";
$qry = @mysql_query($sql, $db) or die("Could not retrieve any results from funcsectors table.");
while($rst = @mysql_fetch_array($qry))
{
	
	$sql2 = "SELECT * FROM func WHERE sector = '".$rst['sid']."' ORDER BY id ";
	$qry2 = @mysql_query($sql2, $db);
	if(@mysql_num_rows($qry2))
	{
		while($rst2 = @mysql_fetch_array($qry2))
		{
			$rows['sector'][] = $rst['heading'];
			$rows['blurb'][] = $rst['blurb'];
			$rows['sid'][] = $rst2['sid'];
			$rows['title'][] = stripslashes($rst2['title']);
			$rows['description'][] = nl2br(stripslashes($rst2['description']));
		}
	}
	else
	{
		$rows['sector'][] = $rst['heading'];
		$rows['blurb'][] = $rst['blurb'];
		$rows['sid'][] = "";
		$rows['title'][] = "";
		$rows['description'][] = "";
	}
}
foreach($rows['sector'] as $key=>$val)
{
	if($key == 0||$val != $rows['sector'][($key-1)])
	{
		echo "\n<h4><a name="".($rows['sid'][$key]+1).""></a>".$val."</h4>\n";
		echo $rows['blurb'][$key]?"<p>".$rows['blurb'][$key]."</p>":"";
	}
	echo $rows['title'][$key]?"<p><b>".$rows['title'][$key]."</b><br/>".$rows['description'][$key]."</p>\n":"";
}

?>
This works but it's what I've been trying to avoid doing by hopefully finding a single SQL query which will get me a table of:

Code: Select all

| SECTOR | SECTORBLURB | JOBTITLE | JOBDESCRIPTION |
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

you can use the outer join, just can't use the subselect in it...try

Code: Select all

SELECT DISTINCT heading, id, title, description, fs.sid AS sid, fs.blurb FROM funcsectors fs LEFT OUTER JOIN func ON sid = sector ORDER BY func.sid, id;
leenoble_uk
Forum Contributor
Posts: 108
Joined: Fri May 03, 2002 10:33 am
Location: Cheshire
Contact:

Post by leenoble_uk »

Hey well done :)
After I translated the line you fed me to this:

Code: Select all

SELECT DISTINCT heading, id, title, description, funcsectors.sid AS sid, funcsectors.blurb FROM funcsectors LEFT OUTER JOIN func ON sid = sector ORDER BY funcsectors.sid, id;
I got what I was looking for.
Aologies for the confusing table name. These jobs are in an area of the site called 'functional expertise' (client's terminology). I should perhaps have renamed them.
Anyway, it worked and I have absolutely no idea why or how. I've never used the JOIN terminology before and it's complete gibberish to me. I don't understand the concept of LEFT OUTER JOIN etc. and the manual isn't much help.
Any clue as to where I can find a decent online resource on these?
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

http://databasejournal.com/ might be a good place...

Outer joins basically take include all the information from a table where the corresponding table may OR MAY NOT have data, like what you had...where the funcsectors table had records that don't have matches in the func table and you wish to show those values..

Inner Joins are the same type of joins as t1.col1 = t2.col1...it takes a little while to grasp the concept, but when you do, you'll find its easier to visualize...
Post Reply