We help IT Professionals succeed at work.

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

RationalRabbit
on
66 Views
Last Modified: 2018-09-07
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

mohan singhWeb developer
CERTIFIED EXPERT

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)
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

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.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

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!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.