CODEWITHSUNDEEP
mysqljoinexists
Brian Leishman
Brian Leishman

Reputation: 8555

Two MySQL queries giving different results

I have these two queries that I'm running. The first one works, but for some reason the EXISTS() function seems to add about a minute of load time, which makes it unbearable to use. So I wrote a second query that I feel should give the same answer, but it gives a very differt answer instead.

First

select 
count(`FavoritesHeaderID`) `count` 
from `favoritesheader` 
join `vwactiveevent` on `vwactiveevent`.`MainEventID`=`favoritesheader`.`MainEventID` 
where `Admin`=0 
and exists(
select 1 
from `invoiceheader` 
join `vwactiveevent` on `vwactiveevent`.`MainEventID`=`invoiceheader`.`MainEventID` 
where `Phone`=`favoritesheader`.`CellPhone` 
and `OrderStatusID`=2
); => 284

Second

select 
count(`FavoritesHeaderID`) `count`
from `favoritesheader` 
join `vwactiveevent` on `vwactiveevent`.`MainEventID`=`favoritesheader`.`MainEventID`
join  `invoiceheader` on `vwactiveevent`.`MainEventID`=`invoiceheader`.`MainEventID` 
where `Admin`=0 
and `Phone`=`favoritesheader`.`CellPhone` 
and `OrderStatusID`=2; => 1578

I don't know if just this is enough information to go off of, but if it is then any help will be much appreciated.

Upvotes: 1

Views: 85

Answers (1)

sgeddes
sgeddes

Reputation: 62841

Chances are the JOIN is including duplicated rows in your COUNT. If I'm understanding your question correctly, assuming FavoritesHeaderID is unique and that's the field you're trying to COUNT, you can add DISTINCT to each query and they should return the same count:

select 
    count(distinct `FavoritesHeaderID`) `count`
from `favoritesheader` 
    join `vwactiveevent` on `vwactiveevent`.`MainEventID`=`favoritesheader`.`MainEventID`
    join  `invoiceheader` on `vwactiveevent`.`MainEventID`=`invoiceheader`.`MainEventID` 
where `Admin`=0 
    and `Phone`=`favoritesheader`.`CellPhone` 
    and `OrderStatusID`=2

Upvotes: 1

Related Questions