SQLite & MySQL - Same Query - Different Resluts - Multiple Joins

Question

I have 2 databases with the same data, but slightly different data types in some fields (eg SQLite DB has TEXT where MySQL DB has varchar(50))

I run the following query:

SELECT * 
FROM audio a, 
  audiocodec ac, 
  fileproperties f, 
  transportstream t, 
  transportservice ts, 
  video v, 
  videocontent vct, 
  videoFrameRate vf, 
  videocodec vc 
WHERE 
f.PK_filename = a.filename 
AND a.codec = ac.PK_audioCodecID 
AND f.PK_filename = a.filename 
AND f.PK_filename = t.filename 
AND t.services = ts.PK_serviceID 
AND f.PK_filename = v.filename 
AND v.content = vct.PK_contentID 
AND v.frameRate = vf.PK_frameRateID 
AND v.codec = vc.PK_videoCodecID

Done in the MySQL DB, I get 10 results as expected. In SQLite, 2 results. Any reasons? (I understand that this maybe difficult to answer be not specifying the datatypes and constraints I have made with the tables)

Answer 1

One thing I noticed is that f.PK_filename = a.filename is twice in your WHERE statement (which is probably not the reason for the different results you get, but still somehow noteworthy).