Left outer join doesn't seem to be working

BJ9 · Jun 26, 2006

I've got a query in which I want the store_ids to show up whether or not there is any data attached. I've tried left outer, right outer, full outer and nothing seems to work. Interestingly enough the second left outer on employees works.

What am I missing?

I'm using PostGresql 7.4.1

Here's my SQL:

Code:

select store.store_id, store.short_name, 
		store.suffix, 
		ia.date, ia.time, ia.sec, 
		ia.transaction_type, ia.item_number, 
		ia.cost, ia.price, ia.category,
		ia.date_in, ia.last_ptn, 
		ia.gunbook_num, ia.gunbook_page, ia.gunbook_line,
		ia.description, ia.quantity, ia.last_ptn,
		rtrim(employee.last_name) as emp_lastname, rtrim(employee.first_name) as emp_firstname
		from inventory_audit ia  
			left outer join store on store.store_id = ia.store_id
			left outer join employee on (ia.userid = employee.userid and ia.store_id = employee.store_id)

	where ia.transaction_type in ('M','A','D') and store.store_id = 84 and ia.date = '2006-06-20'

Thanks

feherke · Jun 26, 2006

Hi

You should specify [tt]right join[/tt] for the first [tt]join[/tt].

For better viewability I prefer to put those tables from which [tt]null[/tt]s could be returned to the end.

Code:

[gray]-- ...[/gray]
[b]from[/b] store
  [b]left join[/b] inventory_audit ia [b]on[/b] store.store_id = ia.store_id
  [b]left join[/b] employee [b]on[/b] (ia.userid = employee.userid and ia.store_id = employee.store_id)
[gray]-- ...[/gray]

Of course, only if I understand you correctly...

Feherke.

http://rootshell.be/~feherke/

BJ9 · Jun 26, 2006

Thanks for the reply

I think you're understanding correctly. I did try both of those variations and am still not getting the store_id when there's no inventory data for it.

Any other ideas on what I could try? :-|

feherke · Jun 26, 2006

Hi

Post a few records from those 3 tables ( as possible, only the fields involved in the joining ). Maybe the table structures too.

Feherke.

http://rootshell.be/~feherke/

ceco · Jun 26, 2006

I am not quite sure if it is what you want, but try something like that

select store.store_id, store.short_name,
store.suffix,
ia.date, ia.time, ia.sec,
ia.transaction_type, ia.item_number,
ia.cost, ia.price, ia.category,
ia.date_in, ia.last_ptn,
ia.gunbook_num, ia.gunbook_page, ia.gunbook_line,
ia.description, ia.quantity, ia.last_ptn,
rtrim(employee.last_name) as emp_lastname, rtrim(employee.first_name) as emp_firstname
from store
left outer join inventory_audit ia on store.store_id = ia.store_id AND ia.transaction_type in ('M','A','D') AND ia.date = '2006-06-20'
left outer join employee on (ia.userid = employee.userid and ia.store_id = employee.store_id)
where store.store_id = 84

BJ9 · Jun 27, 2006

Thanks ceco that worked in the SQL. Unfortunately, when I drop it into my PHP is doesn't work anymore. So that tells me something in my PHP is written wrong.

Thanks everyone for the great help.

BJ

ceco · Jun 27, 2006

how is it exactly doesn't work

i.e. pg_last_error (or if using PDO the equivalent) gives an error, or the results are not correct

give the php code

BJ9 · Jun 30, 2006

Hi ceco,

It doesn't give me an error it just doesn't the store_id if there is no data.

I didn't write this PHP code, I'm just trying to troubleshoot it and being kind of new to PHP I'm having trouble reading exactly where the issue is.

It's rather long but here it is.

Code:

<?
if ($_REQUEST["init"] == "html"){
	ob_start("ob_gzhandler");
} else {
	ob_start();
}
include("../php.lib");

//Report Title
$title=$_REQUEST["promptTITLE"];
$INIT=$_REQUEST["init"];

//Loans Start and End Dates
// Date passed interactively
if ($_REQUEST["dateStartDateYTD"] != "") {
	$d1=$_REQUEST["dateStartDateYTD"];
	$d2=$_REQUEST["dateEndDateYTD"];
	$startdate = $_REQUEST["dateStartDateYTD"];
	$enddate = $_REQUEST["dateEndDateYTD"];
// Date passed by scheduler
} else if ($_REQUEST["datepromptStartDateYTD"] != "") {
	$d1=explode("/",$_REQUEST["datepromptStartDateYTD"]);
	$d2=explode("/",$_REQUEST["datepromptEndDateYTD"]);
	$startdate=$_REQUEST["datepromptStartDateYTD"];
	$enddate=$_REQUEST["datepromptEndDateYTD"];
}

//Stores to query against
$stores = GET_IN_STORES();

$drill = $_REQUEST["drill"];

if ($drill != ""){
	$stores = "('". $_REQUEST["STOREID"]. "')";
}

$DBSTRING = "dbname=data_warehouse host=cis user=" . $_REQUEST["user"] .
       " password='" . $_REQUEST[password]. "'";

//Creating an array of inventory items
$inventory = &inventoryAudit($stores, $startdate, $enddate, $DBSTRING);

if (! $_REQUEST["promptPRINT_PRICAT"]){
//Deleting items that have a modification of category and price
for ($i=0;isset($inventory[$i][0]);$i++){
	if ($i != "0"){
		$prevrow = $inventory[$i-1][3];
		$currow = $inventory[$i][3];
		if (	($prevrow[item_number] == $currow[item_number])
			&& ($prevrow[cost] == $currow[cost])
			&& ($prevrow[date_in] == $currow[date_in])
			&& ($prevrow[last_ptn] == $currow[last_ptn])
			&& ($prevrow[gunbook_num] == $currow[gunbook_num])
			&& ($prevrow[gunbook_page] == $currow[gunbook_page])
			&& ($prevrow[gunbook_line] == $currow[gunbook_line])
			&& ($prevrow[description] == $currow[description])
			&& ($prevrow[quantity] == $currow[quantity])
			&& ($prevrow[last_ptn] == $currow[last_ptn])
			){

			if ($prevrow[category] != $currow[category]){
				$prevrow[category] = "## ". $prevrow[category];
				$currow[category] = "## ". $currow[category];
				$inventory[$i-1][3] = $prevrow;
				$inventory[$i][3] = $currow;
				//$inventory[$i-1][3] = "";
				//$inventory[$i][3] = "";
			}
			if ($prevrow[price] != $currow[price]){
				$prevrow[price] = "##". $prevrow[price];
				$currow[price] = "##". $currow[price];
				$inventory[$i-1][3] = $prevrow;
				$inventory[$i][3] = $currow;
				//$inventory[$i-1][3] = "";
				//$inventory[$i][3] = "";
			}
		}
	}
}
}

$OUTPUT;

$span = "11";
$tfsize='-1';
$fsize='-1';
if ($INIT == "pdf"){
	$width="625";
	$tfsize='-2';
	$fsize='-2';
} else {
	$width="900";
}

global $title;
global $previnum, $prevstoreid;

if ($INIT != "xls"){
	$OUTPUT .= "<center><table border=1 cellpadding=2 cellspacing=0 width=$width>";
	$OUTPUT .= ("<tr bgcolor=\"000066\"><td  align=center colspan=$span><br><b><font size=\"+1\" color=white>$title</font></b>".
		"<br><font color=white>$startdate - $enddate</font><br><br></td></tr>\n");
	$OUTPUT .= "<tr bgcolor=\"CCCCCC\">".
			"<td align=left><font size=$tfsize><b>STORE</b></font></td>".
			"<td align=center><font size=$tfsize><b>TYPE</b></font></td>".
			"<td align=center><font size=$tfsize><b>ITEM NO</b></font></td>".
			"<td align=center><font size=$tfsize><b>CAT</b></font></td>".
			"<td align=center><font size=$tfsize><b>DATE</b></font></td>".
			"<td align=center><font size=$tfsize><b>TIME</b></font></td>".
			"<td align=center><font size=$tfsize><b>TKT NUM</b></font></td>".
			"<td align=center><font size=$tfsize><b>EMPLOYEE</b></font></td>".
			"<td align=center><font size=$tfsize><b>QTY</b></font></td>".
			"<td align=center><font size=$tfsize><b>COST</b></font></td>".
			"<td align=right><font size=$tfsize><b>PRICE</b></font></td>".
			"</tr>\n";
}

$prevstoreid = "";
for ($i=0;isset($inventory[$i][0]);$i++){
	$OUTPUT .= invAudit3($inventory[$i][3]);
}

if ($INIT != "xls"){
	$OUTPUT .= "</table></center>";
}

switch ($INIT) {
	case "pdf":
		ob_end_clean();
            header('Content-type: application/pdf');
            flush();
            $filename=tempnam("/tmp","invmod");
            $fp=fopen($filename,"w");
            fwrite($fp,$OUTPUT);
            fclose($fp);
		passthru("/usr/bin/htmldoc -t pdf14 --quiet --webpage".
			" --left .25in --right .25in --bottom .25in --top .25in $filename");
            exec("rm -f $filename");
		//echo "PDF not currently supported.";
		break;
	case "xls":
		ob_end_clean();
		header('Content-type: application/vnd.ms-excel');
		flush();
		print $OUTPUT;
		//echo "XLS not currently supported.";
		break;
	case "rtf":
                ob_end_clean();
                header('Content-type: application/msword');
                flush();
                print $OUTPUT;
                //echo "XLS not currently supported.";
                break;	
	default: //HTML
		//ob_end_flush();
		print $OUTPUT;
		break;
} //end switch ($INIT)

function &findInventoryAuditItem($stores, $startdate, $enddate, $dbstring) {
	global $drill, $prevstoreid;
	$sql = "
		select store.store_id, store.short_name, 
		store.suffix, 
		ia.date, ia.time, ia.sec, 
		ia.transaction_type, ia.item_number, 
		ia.cost, ia.price, ia.category,
		ia.date_in, ia.last_ptn, 
		ia.gunbook_num, ia.gunbook_page, ia.gunbook_line,
		ia.description, ia.quantity, last_ptn,
		rtrim(employee.last_name) as emp_lastname, rtrim(employee.first_name) as emp_firstname
		
		from store
			left outer join inventory_audit ia on store.store_id = ia.store_id 
			left outer join employee on (ia.userid = employee.userid and ia.store_id = employee.store_id)

		where ia.store_id in $stores
			and ia.date >= '$startdate'
			and ia.date <= '$enddate'
			and ia.transaction_type in ('M','A','D')
			 
		order by store.sort_id, ia.date, ia.time, ia.sec, ia.item_number
	";

	$dbconn=pg_connect($dbstring);
	$result = pg_exec($dbconn,$sql);

	return $result;
}

function &inventoryAudit($stores, $startdate, $enddate, $DBSTRING) {
	$result = &findInventoryAuditItem($stores, $startdate, $enddate, $DBSTRING);
	$ia;
	$ctr = "0";

	while($row = pg_fetch_assoc($result)){
		//echo "\n<br>$ctr\n";
		$ia[$ctr][0] = $row[date];
		$ia[$ctr][1] = $row[time];
		$ia[$ctr][2] = "invaud";
		$ia[$ctr][3] = $row;
		$ctr++;
	}

	return $ia;
}

$z=0;

function invAudit3 ($row){
	global $span, $tfsize, $fsize, $title, $previnum, $width, $prevstoreid, $INIT, $z;
	//echo $INIT;

	$type="";

	switch ($row[transaction_type]){
		case "A":
			$type = "Add";
			break;
		case "B":
			$type = "Purchased";
			break;
		case "C":
			$type = "Confiscated";
			break;
		case "D":
			$type = "Deleted";
			break;
		case "M":
			$type = "Modified";
			break;
		case "P":
			$type = "Pull";
			break;
		case "R":
			$type = "Repair";
			break;
		default:
			$type = "$row[transaction_type]";
			break;
	}

	if ($INIT != "xls"){

		if (($previnum != $row[item_number]) && ($previnum != "") 
			&& (substr($row[category],0,2) != "##") && (substr($row[price],0,2) != "##")){
			//$OUTPUT.= "</table><br>\n<table border=1 cellpadding=2 cellspacing=0 width=$width>";
			$OUTPUT .= "<tr><td colspan=$span>&nbsp;</td></tr>\n";
		}
		$previnum = $row[item_number];

		if ($prevstoreid != $row[store_id]){
			$OUTPUT .= "<tr bgcolor=\"9999FF\"><td align=left colspan=$span><b>$row[store_id]. $row[short_name]</b></td></tr>\n";
		}
		$prevstoreid = $row[store_id];

		if ((substr($row[category],0,2) != "##") && (substr($row[price],0,2) != "##")) {

			$da = explode("-", $row[date]);
			$ta = explode(".", $row[time]);

			if ($ta[0] > 12) {
				$row[time] = ($ta[0]-12). ":". $ta[1]. " PM";
			} else if ($ta[0] == 12) {
				$row[time] = $ta[0]. ":". $ta[1]. " PM";
			} else if ($ta[0] < 12) {
				$row[time] = $ta[0]. ":". $ta[1]. " AM";
			}

			$OUTPUT .= "\t<tr bgcolor=e6e6e6>\n".
				"\t\t<td align=left><font size=$fsize>$row[suffix]</font></td>\n".
				"\t\t<td align=center><font size=$fsize>$type</font></td>\n";
			if (($INIT != "pdf") && ($INIT != "rtf")){
                        	$OUTPUT .= "\t\t<form action=\"invaudititem.php\" name=\"lnk$z\" method=post>\n".
                            		"\t\t<INPUT TYPE=\"HIDDEN\" NAME=\"user\" VALUE=\"". $_REQUEST['user']. "\">\n".
                            		"\t\t<INPUT TYPE=\"HIDDEN\" NAME=\"password\" VALUE=\"". $_REQUEST['password']. "\">\n";
				$OUTPUT .= "\t\t<INPUT TYPE=\"HIDDEN\" NAME=\"drill\" VALUE=\"item\">\n";
                        	$OUTPUT .=  "\t\t<INPUT TYPE=\"HIDDEN\" NAME=\"dateStartDateYTD\" VALUE=\"". $startdate. "\">\n".
                            		"\t\t<INPUT TYPE=\"HIDDEN\" NAME=\"dateEndDateYTD\" VALUE=\"". $enddate. "\">\n".
                            		"\t\t<INPUT TYPE=\"HIDDEN\" NAME=\"promptTITLE\" VALUE=\"Item History\">\n".
                            		"\t\t<INPUT TYPE=\"HIDDEN\" NAME=\"promptITEMNUMBER\" VALUE=\"".$row[item_number]."\">\n";
				$OUTPUT .= "\t\t<td align=center><font size=$fsize>";
                        	$OUTPUT .= "<a href=\"javascript: document.lnk$z.submit()\">".$row[item_number]."</a>";
                        	$OUTPUT .= "</font></td>\n". "\t\t</form>\n";			
				$z++;
			} else {
				$OUTPUT .= "\t\t<td align=center><font size=$fsize>$row[item_number]</font></td>\n";
			}
			
			$OUTPUT.= "\t\t<td align=center><font size=$fsize>$row[category]</font></td>\n".
				"\t\t<td align=center><font size=$fsize>$da[1]/$da[2]/$da[0]</font></td>\n".
				"\t\t<td align=center><font size=$fsize>$row[time]</font></td>\n".
				"\t\t<td align=center><font size=$fsize>$row[last_ptn]</font></td>\n".
				"\t\t<td align=center><font size=$fsize>&nbsp;$row[emp_firstname] $row[emp_lastname]</font></td>\n".
				"\t\t<td align=center><font size=$fsize>$row[quantity]</font></td>\n".
				"\t\t<td align=center><font size=$fsize>$row[cost]</font></td>\n".
				"\t\t<td align=right><font size=$fsize>$row[price]</font></td>\n".
				"\t</tr>\n";
		
			$OUTPUT .= "\t<tr bgcolor=white><td colspan=$span><font size=$fsize>$row[description]</font></td></tr>\n";

			if (($row[gunbook_num] != "")  && ($row['gunbook_num'] != "0")){
				$OUTPUT .= "\t<tr bgcolor=white><td colspan=$span><font size=$fsize>".
					"Gun Book: $row[gunbook_num] Page: $row[gunbook_page]". 
					" Line: $row[gunbook_line]</font></td></tr>\n";
			}
		}

	} //end if($INIT != "xls")

	if ($INIT == "xls"){
		if ($previnum == ""){
			$OUTPUT .=
				"\t\"STR ID\"".
				"\t\"STORE\"".
				"\t\"TYPE\"".
				"\t\"ITM NO\"".
				"\t\"CAT NO\"".
				"\t\"DATE\"".
				"\t\"HR.MIN\"".
				"\t\"SEC\"".
				"\t\"LST PTN\"".
				"\t\"EMPLOYEE\"".
				"\t\"QTY\"".
				"\t\"COST\"".
				"\t\"PRICE\"".
				"\t\"DESCRIPTION\"".
				"\t\"GUN NO\"".
				"\t\"GUN PG\"".
				"\t\"GUN LN\"\n";
		}

		$previnum = $row[item_number];
		$OUTPUT .=
			"\t\"$row[store_id]\"".
			"\t\"$row[suffix]\"".
			"\t\"$row[transaction_type]\"".
			"\t\"$row[item_number]\"".
			"\t\"$row[category]\"".
			"\t\"$row[date]\"".
			"\t\"$row[time]\"".
			"\t\"$row[sec]\"".
			"\t\"$row[last_ptn]\"".
			"\t\"$row[emp_firstname] $row[emp_lastname]\"".
			"\t\"$row[quantity]\"".
			"\t\"$row[cost]\"".
			"\t\"$row[price]\"".
			"\t\"$row[description]\"".
			"\t\"$row[gunbook_num]\"".
			"\t\"$row[gunbook_page]\"".
			"\t\"$row[gunbook_line]\"\n";
	}

	return $OUTPUT;
}

?>

If you can figure it out or point me in the right direction I would greatly appreciate it.

BJ

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Left outer join doesn't seem to be working

BJ9

MIS

feherke

Programmer

BJ9

MIS

feherke

Programmer

ceco

IS-IT--Management

BJ9

MIS

ceco

IS-IT--Management

BJ9

MIS

Similar threads

Part and Inventory Search

Sponsor