Page 1 of 1

I think I need a JOINt

Posted: Thu May 13, 2004 5:33 am
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.

Posted: Thu May 13, 2004 7:18 am
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

Posted: Thu May 13, 2004 8:57 am
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 |

Posted: Thu May 13, 2004 11:45 am
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;

Posted: Thu May 13, 2004 12:10 pm
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?

Posted: Thu May 13, 2004 12:23 pm
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...