MySQL Query not pulling up

The query below doesn't pull any records:

select Ledgers.personID , (0 - sum(ifnull(paidOut,0)) - sum(ifnull(debit,0)) + sum(ifnull(credit,0))) as totLedger, CONCAT(strLastName, ', ', strFirstName) as FullName from Ledgers left join People on People.personID = Ledgers.personID WHERE People.intActive = 1 AND People.strPersonOffice = 'stp' group by Ledgers.personID, strLastName, strFirstName order by strLastName, strFirstName

This one does:
select Ledgers.personID , (0 - sum(ifnull(paidOut,0)) - sum(ifnull(debit,0)) + sum(ifnull(credit,0))) as totLedger, CONCAT(strLastName, ', ', strFirstName) as FullName from Ledgers left join People on People.personID = Ledgers.personID WHERE People.intActive = 0 AND People.strPersonOffice = 'stp' group by Ledgers.personID, strLastName, strFirstName order by strLastName, strFirstName

The only difference is People.intActive = 1

Using MySQL and PHP.

Yes... there are "People" with "intActive" set as 1.  This doesn't work if intActive is a TinyInt or Int.
Sheldon LivingstonConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Please post the CREATE TABLE statement and a few lines of test data so we can reproduce the error condition, thanks.
0
Sheldon LivingstonConsultantAuthor Commented:
Not sure how to get the CREATE statement
0
Sheldon LivingstonConsultantAuthor Commented:
People table:
CREATE TABLE `People` (
 `strSSN` varchar(20) NOT NULL,
 `personID` int(11) NOT NULL AUTO_INCREMENT,
 `strDL` varchar(20) DEFAULT NULL,
 `DLState` varchar(2) DEFAULT NULL,
 `strFirstName` varchar(15) NOT NULL,
 `strLastName` varchar(20) NOT NULL,
 `strPersonAddress_1` varchar(30) DEFAULT NULL,
 `strPersonAddress_2` varchar(30) DEFAULT NULL,
 `strPersonCity` varchar(15) DEFAULT NULL,
 `strPersonState` varchar(2) DEFAULT NULL,
 `strPersonZip_Code` varchar(10) DEFAULT NULL,
 `strPersonPhone_Number` varchar(15) DEFAULT NULL,
 `strGender` varchar(6) DEFAULT NULL,
 `datBirth_Date` datetime DEFAULT NULL,
 `datStart_Date` datetime DEFAULT NULL,
 `strPersonOffice` varchar(20) DEFAULT NULL,
 `intAgreement` tinyint(4) NOT NULL,
 `intApproved` tinyint(4) NOT NULL,
 `intActive` int(11) NOT NULL,
 `onDeal` tinyint(4) DEFAULT NULL,
 `strOperator_Stamp` varchar(20) DEFAULT NULL,
 `datDate_Stamp` datetime DEFAULT NULL,
 PRIMARY KEY (`personID`)
) ENGINE=MyISAM AUTO_INCREMENT=50081 DEFAULT CHARSET=latin1

Ledgers table:
CREATE TABLE `Ledgers` (
 `transID` int(11) NOT NULL AUTO_INCREMENT,
 `personID` int(11) DEFAULT NULL,
 `ledgerDate` datetime DEFAULT NULL,
 `description` varchar(60) DEFAULT NULL,
 `paidOut` decimal(12,2) DEFAULT NULL,
 `debit` decimal(12,2) DEFAULT NULL,
 `credit` decimal(12,2) DEFAULT NULL,
 `dateCreated` datetime DEFAULT NULL,
 `creatorID` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`transID`)
) ENGINE=MyISAM AUTO_INCREMENT=300212 DEFAULT CHARSET=latin1

No error message shows... A test that I do is change intActive from 0 to 1 from an existing result query and when using 1 nothing shows.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

PortletPaulfreelancerCommented:
You are causing an "implicit inner join" through the WHERE clause. This means the LEFT JOIN is overridden by the predicates of the where clause, which produces the same result as if you had used an INNER JOIN
SELECT
      Ledgers.personID
    , (0 - SUM(ifnull(paidOut, 0)) - SUM(ifnull(debit, 0)) + SUM(ifnull(credit, 0))) AS totLedger
    , CONCAT(strLastName, ', ', strFirstName) AS FullName
FROM Ledgers
      INNER JOIN People ON People.personID = Ledgers.personID
WHERE People.intActive = 0
      AND People.strPersonOffice = 'stp'
GROUP BY
      Ledgers.personID
    , strLastName
    , strFirstName
ORDER BY
      strLastName
    , strFirstName

Open in new window

You could try using more JOIN CONDITIONS instead e.g.
SELECT
      Ledgers.personID
    , (0 - SUM(ifnull(paidOut, 0)) - SUM(ifnull(debit, 0)) + SUM(ifnull(credit, 0))) AS totLedger
    , CONCAT(strLastName, ', ', strFirstName) AS FullName
FROM Ledgers
      LEFT JOIN People ON People.personID = Ledgers.personID
                  AND People.intActive = 1
                  AND People.strPersonOffice = 'stp'
GROUP BY
      Ledgers.personID
    , strLastName
    , strFirstName
ORDER BY
      strLastName
    , strFirstName
;

Open in new window

But I have to admit I would really want to see the result of this:

select intActive , count(*)
from People
group by intActive

Are there definitely rows where intActive = 1 ?
0
arnoldCommented:
On the same path as Paul.
Another test would be to add the strPersonOffice

select intActive, strPersonOffice,count(*) as counter from people group by intActive,strPersonOffice
0
Sheldon LivingstonConsultantAuthor Commented:
Paul... the result of your query showed:

0   26654
1   2

arnold... your query simply broke down the numbers (26656) into the 38 different offices.
0
PortletPaulfreelancerCommented:
WHERE People.intActive = 1
      AND People.strPersonOffice = 'stp'


Is there a row for 'stp' and intActive=1 from arnold's query?

How many are counted for that combination?

Perhaps no rows exist that meet all the filtering criteria
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sheldon LivingstonConsultantAuthor Commented:
I feel that both Paul and arnold should share the points...

Classic goofball mistake.  

My test records were NOT setup for the stp office... the query is working fine... I logged in as the correct office and all is well :-/
0
PortletPaulfreelancerCommented:
Please don't ignore the fact that with the SQL as given in your question that LEFT JOIN is ignored and you get the equivalent of an INNER JOIN

If that impact is what you want then change the join to INNER JOIN (its more efficient this way)

If the impact is NOT what you want, then you should move the conditions to the join instead of the where clause.

If you don't understand the point I'm making please let me know.
0
Sheldon LivingstonConsultantAuthor Commented:
Thank you for the assist.
0
Sheldon LivingstonConsultantAuthor Commented:
FYI Paul Maxwell, I don't understand how to do that.
0
arnoldCommented:
I think the where clause you have converts the left join to inner join in terms of resulting set.
What Paul is suggesting instead of the where caluse at the end of the complete query, you add the restrictions in the left join on parameter1 and parameter2 and parameter3

In the case here i am not sure it would have made a difference because of the absence of a representative sample
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.