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!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Left outer join doesn't seem to be working

Status
Not open for further replies.
BJ9

BJ9

MIS
Joined
Feb 10, 2006
Messages
54
Location
US
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
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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? :-|
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Marclem
  • Locked
  • Question Question
Join on 4 tables
Replies
0
Views
339
Marclem
Marclem
wbower34
  • Locked
  • Question Question
Issues with table join
Replies
2
Views
689
wbower34
wbower34
MCP2000
  • Locked
  • Question Question
Show records NOT BETWEEN date/timestamps
Replies
2
Views
804
MCP2000
MCP2000
rs120p
  • Locked
  • Question Question
Which way to move pg_xlog why sym link and not Linux Mount point?
Replies
0
Views
557
rs120p
rs120p
MrCBofBCinTX
  • Locked
  • Question Question
I need to pull out down long tree of tables to bottom
Replies
0
Views
497
MrCBofBCinTX
MrCBofBCinTX

Part and Inventory Search

Sponsor

Back
Top