CODEWITHSUNDEEP
mysql
lbennet
lbennet

Reputation: 1103

Query logic, wrong desired results mysql

SELECT * FROM 
MobileApps as dtable 
WHERE (SELECT COUNT(*) as c 
       FROM app_details
       WHERE trackId=dtable.SourceID)=0 
ORDER BY id ASC 
LIMIT 0,2

Problem is say the first two results ordered by id are in app_details, so the COUNT(*) doesnt' equal to 0 for the first two results. But there are much more results available in MobileApps table that would equal to 0.

I supposed it would first SELECT * FROM app_details WHERE trackId=dtable.SourceID)=0 and then ORDER BY id ASC LIMIT 0,2, not the other way around, what is a possible way to get it around ?

Thanks

Upvotes: 0

Views: 23

Answers (1)

Barmar
Barmar

Reputation: 780949

Your query works, but a better way to write it is:

SELECT dtable.*
FROM MobileApps dtable
LEFT JOIN app_details d ON d.trackId = dtable.SourceID
WHERE d.trackId IS NULL
ORDER BY dtable.id
LIMIT 0, 2

or:

SELECT *
From MobileApps dtable
WHERE NOT EXISTS (SELECT *
                  FROM app_details d
                  WHERE d.trackId = dtable.SourceID)
ORDER BY id
LIMIT 0, 2

See all 3 versions here: http://www.sqlfiddle.com/#!2/536db/2

For a large table, you should probably benchmark them to see which one MySQL optimizes best.

Upvotes: 1

Related Questions