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.
PHP

Avatar of undefined
Last Comment
RationalRabbit

8/22/2022 - Mon
mohan singh

I Think you should be use RIGHT OUTER JOIN


Thank you

Regards Mohan Singh
ASKER
RationalRabbit

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

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)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Chris Stanyon

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.
ASKER
RationalRabbit

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
ASKER CERTIFIED SOLUTION
Chris Stanyon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
RationalRabbit

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Stanyon

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

Yep, that was the problem. Thanks, Chris!
ASKER
RationalRabbit

Thanks!
Your help has saved me hundreds of hours of internet surfing.
fblack61