Inner Join in MySQL Not Displaying Some Fields When One Condition Is Added

RationalRabbit
RationalRabbit used Ask the Experts™
on
I am at a loss as to why this inner join is not working.

       $Conn = mysql_connect($HOST, $USER, $PASSWORD);
       mysql_select_db($DBName, $Conn);
    
    $Q = "
        SELECT
            Credit.ID,
            Credit.UserName,
            Credit.Contact,
            Credit.Phone,
            Credit.Status,
            Credit.OriginDate,
            Credit.Approval,
            Accounts.StoreTitle,
            Accounts.AppType,
            Accounts.AcctName
        FROM
            Accounts
        INNER JOIN
            Credit ON Credit.ID = Accounts.AppID
        WHERE
            Credit.UserName = '$_SESSION[User]'
            AND
                (Credit.Approval IS NULL OR Credit.Approval = ' ')
            AND
                Accounts.AppType != 'Location'
            AND
                CreditApp2.Status = 'Store'
           ORDER BY
                AccountsMain.StoreTitle";
    $Result = mysql_query($Q,$Conn);
    if(!$Result){$Error[] = "Database Error!";}
    else
    {
       if(mysql_num_rows($Result) > 0)
       {
          while($A = mysql_fetch_array($Result))
          {
             $ODate = dFormat($A['OriginDate'],5);
             $StTitle = strlen($A['StoreTitle']) > 34 ? substr($A['StoreTitle'],0,32).'...' : $A['StoreTitle'];
             $Cust = strlen($A['AcctName']) > 34 ? substr($A['AcctName'],0,32).'...' : $A['AcctName'];
             echo('
             <a class="StoreRow" href="somefile.php?AppID='.$A['ID'].'">
                <div style="clear:left; float:left; width:240px; text-align:left;">'.$StTitle.'</div>
                <div style="float:left; width:240px; text-align:left;">'.$Cust.'</div>
                <div style="float:left; width:50px;">'.$A['AppType'].'</div>
                <div style="float:left; width:70px; text-align:left;">'.$A['UserName'].'</div>
                <div style="float:left; width:50px; text-align:left;">'.$A['Status'].'</div>
                <div style="float:left; width:70px; text-align:left;">'.$ODate.'</div>
             </a>');
          }
      }
   }

Open in new window

The query works fine until it gets to

    (Credit.Approval IS NULL OR Credit.Approval = ' ')

What this means is that if I run the query without that phrase, all fields ($A[ID], $STitle, $Cust, $A[AppType], $A[UserName], $A[Status], $ODate) will display.

If I run it with that phrase, the only fields that will display are $A[ID], $A[UserName], $A[Status], $ODate, which are the fields from the Credit table. The fields **not** showing are from the Accounts table.
 
The Approval field is valid.

---

Just so I don't have to explain later, Yes, this is mysql (not mysqli or PDO) and yes, I know it is obsolete. It is what I have to work with.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I Think you should be use RIGHT OUTER JOIN


Thank you

Regards Mohan Singh

Author

Commented:
There is actually more to the query, which I just added.  I'll have to read up on RIGHT OUTER JOIN.

Author

Commented:
Doesn't seem to make a difference.
I should mention that all ID's in Credit have a matching AppID in Accounts. The Accounts table simply holds the account information (Company name, address, etc) It is used in other queries with other tables.
Ideally, the "ID" would be in Accounts, with AppID being the foreign key, but, due to the original code, the ID in Credit is the originating ID key. (Just an explanation)
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
When you say the columns won't display, I'm assuming you mean the columns are empty.

Without seeing your data, it's difficult to say. For example, it looks like those Account columns are blank in the DB for Credit records that don't have an Approval.

Author

Commented:
If they don't have an Approval, they do not appear on the list. For example
Without the condition, since Approval has only 2 conditions (the equivalent of true or false) the list will appear like this:
(The only difference is that records with an approval will show as well as those that don't)
File1.png
With the condition, since I haven't formatted for blank entries (there shouldn't be any), it will look like this:
(The Accounts columns are not produced by the query)
File2.png
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
OK. First off, there looks to be a couple of typos in your code that need fixing.

You're missing a closing parenthesis from the while clause:

while($A = mysql_fetch_array($Result) )

In your output, you have the wrong variable name:

text-align:left;">'.$STitle.'</div>

It should be $StTitle

You should turn on error reporting at the start of your script. Add this right at the beginning:

error_reporting(E_ALL);
ini_set('display_errors', 1);

Open in new window

Fix those issues and run your code again. Currently, those screengrabs of rows don't seem to match the code that you've posted.

You say the Accounts columns aren't being generated by the DB - pretty sure that's not correct. For debugging purposes, instead of outputting  some formatted HTML, just do a simple var_dump() of the data and see what you get:

while($A = mysql_fetch_array($Result)
{
    var_dump($A);
}

Open in new window

Author

Commented:
Sorry for the typos. They are not in the original code, though. I'll correct them if I can still edit the question.

Seems that I have jumped to some conclusions here.  Originally, all this data was in one file. The accounts data was removed from that file and moved to the new Accounts file. It appears that, for some reason, the newer, un-Approved records are missing StoreTitles and CompanyNames and those were the only records that showed up when the condition was active. :)

Yes, the simulated lists should not have shown CompanyName.

Thanks for making me think. Should have done a var dump in the first place. At the end of an 18-hour day. Will address this in the AM but yes, it appears you are correct. The problem appears to be in the database.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Haha - developing after 18 hours always benefits from fresh eyes (or a really big cup of coffee!)

Author

Commented:
Yep, that was the problem. Thanks, Chris!

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial