CODEWITHSUNDEEP
mysqlsql
Lamin Barrow
Lamin Barrow

Reputation: 879

SQL Query producing wrong count result

I have the following SQL query

SELECT
  DISTINCT
  count("SiteTree_Live"."ID") 
FROM  
  "SiteTree_Live"
  LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID"
  LEFT JOIN "TourPage_Live" ON "TourPage_Live"."ID" = "SiteTree_Live"."ID"
  LEFT JOIN "DepartureDate" ON "DepartureDate"."TourID" = "SiteTree_Live"."ID"
WHERE 
  ("SiteTree_Live"."Locale" = 'en_AU')
  AND ("SiteTree_Live"."ClassName" IN ('TourPage'))
  AND ("DepartureDate"."DepartureDate" LIKE '2012-11%')

but it producing a wrong count as the query result. The total intented result this query is suppose to return should not be more than 245 but currently, its returning more than about "4569" results.

Thats is because of the JOIN on the "DepartureDate" table as the query returns the expected result when i remove the join from the "DepartureDate" table.

What modification do i need to make to my query to count the Macthes between "SiteTree_Live"."ID" and "DepartureDate"."TourID" whiles counting only the "SiteTree_Live"."ID" count excluding the Departure dates?

Any suggestions welcomed :)

THE ANSWER

SELECT 
COUNT(DISTINCT SiteTree_Live.ID) 
FROM 
"SiteTree_Live" LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID" 
LEFT JOIN "TourPage_Live" ON "TourPage_Live"."ID" = "SiteTree_Live"."ID" 
LEFT JOIN "DepartureDate" ON "DepartureDate"."TourID" = "SiteTree_Live"."ID" 
WHERE 
("SiteTree_Live"."Locale" = 'en_AU') 
AND ("SiteTree_Live"."ClassName" IN ('TourPage'))
AND ("DepartureDate"."DepartureDate" LIKE '2013-03%')

Seems to give me the right result. Thanks for the tip @Michael Berkowski

Upvotes: 2

Views: 174

Answers (2)

wildplasser
wildplasser

Reputation: 44240

Minor correction: if DepartureDate is a date-type, then the LIKE '2013-03% will force it to be coerced into a character type (this is a mysql feature) As a result, any indexes on DepartureDate will not be used, IIRC. Better use a plain range-query:

SELECT 
  COUNT(DISTINCT stl.ID) 
FROM 
  SiteTree_Live stl
LEFT JOIN 
  DepartureDate dd ON dd.TourID = stl.ID 
WHERE 
  stl.Locale = 'en_AU'
  AND stl.ClassName = 'TourPage'
  AND dd.DepartureDate >= '2013-03-01'
  AND dd.DepartureDate < '2013-04-01'
   ;

Upvotes: 1

chrislondon
chrislondon

Reputation: 12031

Do this (You have a bunch of unneeded joins)

SELECT 
  COUNT(DISTINCT SiteTree_Live.ID) 
FROM 
  `SiteTree_Live`
LEFT JOIN 
  `DepartureDate` ON `DepartureDate`.`TourID` = `SiteTree_Live`.`ID` 
WHERE 
  `SiteTree_Live`.`Locale` = 'en_AU'
  AND `SiteTree_Live`.`ClassName` = 'TourPage'
  AND `DepartureDate`.`DepartureDate` LIKE '2013-03%'

You could also do a GROUP BY:

SELECT 
  COUNT(SiteTree_Live.ID) 
FROM 
  `SiteTree_Live`
LEFT JOIN 
  `DepartureDate` ON `DepartureDate`.`TourID` = `SiteTree_Live`.`ID` 
WHERE 
  `SiteTree_Live`.`Locale` = 'en_AU'
  AND `SiteTree_Live`.`ClassName` = 'TourPage'
  AND `DepartureDate`.`DepartureDate` LIKE '2013-03%'
GROUP BY
  SiteTree_Live.ID

Upvotes: 1

Related Questions