Link to home
Start Free TrialLog in
Avatar of RationalRabbit
RationalRabbit

asked on

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

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.
Avatar of mohan singh
mohan singh
Flag of India image

I Think you should be use RIGHT OUTER JOIN


Thank you

Regards Mohan Singh
Avatar of RationalRabbit
RationalRabbit

ASKER

There is actually more to the query, which I just added.  I'll have to read up on RIGHT OUTER JOIN.
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)
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.
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)
User generated image
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)
User generated image
ASKER CERTIFIED SOLUTION
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Haha - developing after 18 hours always benefits from fresh eyes (or a really big cup of coffee!)
Yep, that was the problem. Thanks, Chris!
Thanks!