Three tables, find the missing jobs - JOIN challenge? 1

[JustKIDn]

Here are the tables;

tbl_people
- pkey - primary key
- dept - varchar
- name - varchar
etc...

tbl_jobs
- jkey - primary key
- desc - varchar (job titles)

tbl_job_link
- lkey - primary key
- ljkey - foreign key (ties with tbl_jobs.jkey)
- lpkey -foreign key (ties with tbl_people.pkey)


Challenge;

I would like to know every job that is not assigned to a person for a given dept.

i.e. if no-one in the maintenance dept is assigned to the job of teaching I want it to print the job and dept.

Sounds like a join query to me but I can't quite get it to work.

Can anyone help?

____________________________
JustKIDn

____________________________
Families can be together forever...

 

[ArkM]

MS Access generated SQL:

SELECT DISTINCT tbl_jobs.desc
FROM (tbl_job_link INNER JOIN tbl_jobs ON
tbl_job_link.ljkey = tbl_jobs.jkey) INNER JOIN tbl_people
ON tbl_job_link.lpkey = tbl_people.pkey
WHERE (((tbl_people.dept)<>"1st dept"))

May be it helps...

 

[JustKIDn]

ArkM,

Thanks, but that didn't get me any closer than I was.

I'll try to explain differently;

tbl_jobs has a list of all the different jobs.

tbl_job_link just has links from tbl_people to tbl_jobs.

If a person had no jobs, there would be no record for him in tbl_job_link (because there would be no need to link him to the job table)

On the other hand someone else could have multiple jobs, so he would have multiple records in tbl_job_link, each with a link to a different job.

What I am trying to do, is walk through the jobs table and compare against the people table for any job that isn't assigned to someone in a given dept.

So just list (print) any jobs that has no one assigned to it in a given dept.

I hope that is more clear?

____________________________
JustKIDn

____________________________
Families can be together forever...

 

[MattNeeley]

$sql = mysql_query("SELECT * FROM `tbl_jobs`");
if (!$sql) {
	//No Connection to the Database.
} else {
	//Connection is OK.
	$sql_num_rows = mysql_num_rows($sql);
	if ($sql_num_rows <= 0) {
		//tbl_Jobs is Empty.
	} else {
		//Get Jobs and step through the next table.
		while ($sql_array = mysql_fetch_array($sql)) {
			$job_key = $sql_array['jkey'];
			$job_desc = $sql_array['desc'];
			$sql = mysql_query("SELECT * FROM `tbl_job_link` WHERE `ljkey`='$job_key'");
			if (!$sql) {
				//No Connection.
			} else {
				//Connection OK.
				$sql_num_rows = mysql_num_rows($sql);
				if ($sql_num_rows <= 0) {
					//Job not assigned
					print $job_desc;
				}
			}
		}
	}
}

 

[JustKIDn]

MattNeeley,

Thanks for that. I had thought (briefly) about reading the jobs table into an array and then cycle through it and compare against the dept.

I modified what you gave me to include the dept. (you forgot the dept!)

print "Results from query <br />";

$sql = mysql_query("SELECT * FROM `tbl_jobs`");
if (!$sql) {
    //No Connection to the Database.
} else {
    //Connection is OK.
    $sql_num_rows = mysql_num_rows($sql);
        
    if ($sql_num_rows <= 0) {
        //tbl_jobs is Empty.
    } else {
        //Get jobs and step through the next table.
        while ($sql_array = mysql_fetch_array($sql)) {
            $job_key = $sql_array['jkey'];
            $job_desc = $sql_array['desc'];
            $sqlqry = mysql_query("SELECT tbl_job_link.*,
            tbl_people.pkey, tbl_people.dept 
            FROM `tbl_job_link`, `tbl_people` 
            WHERE `ljkey`='$job_key' 
            AND `lpkey` = `pkey`
            AND `dept` = 'Maint'");
            if (!$sqlqry) {
                //No Connection.
            } else {
                //Connection OK.
                $sql_num_rows = mysql_num_rows($sqlqry);
                if ($sql_num_rows <= 0) {
                    //Jobs not assigned
                    print "<br />".$job_desc;
                }
            }
        }
    }
}


mysql_close();

This code works perfectly. However, I really was trying to do it without an array. Something you can just type quick in a query window.

I was talking to a friend, and he said it can't be done without another association table between tbl_jobs and another table called tbl_dept.

It seems like a simple solution, so I might go with it. But it will impact a couple of other things in my program which I really need to address anyway.

Thanks MattNeeley, it was a good answer, so you get a S T A R !

____________________________
JustKIDn

____________________________
Families can be together forever...