×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Left outer join doesn't seem to be working

Left outer join doesn't seem to be working

Left outer join doesn't seem to be working

(OP)
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

RE: Left outer join doesn't seem to be working

Hi

You should specify right join for the first join.

For better viewability I prefer to put those tables from which nulls could be returned to the end.

CODE

-- ...
from store
  left join inventory_audit ia on store.store_id = ia.store_id
  left join employee on (ia.userid = employee.userid and ia.store_id = employee.store_id)
-- ...
Of course, only if I understand you correctly...

Feherke.
http://rootshell.be/~feherke/

RE: Left outer join doesn't seem to be working

(OP)
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? :-|

RE: Left outer join doesn't seem to be working

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/

RE: Left outer join doesn't seem to be working

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

RE: Left outer join doesn't seem to be working

(OP)
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

RE: Left outer join doesn't seem to be working

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

RE: Left outer join doesn't seem to be working

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close