CODEWITHSUNDEEP
sqlsqlite
nf313743
nf313743

Reputation: 4237

SQLite - SELECT query returning more than expected

In the following my SQLite database has 10 values in each table. However, when performing my query there are around 100 results returned.

 SELECT videoformat, audioformat FROM videocodec, audiocodec

Any ideas with why it is doing this?

Thanks

Upvotes: 0

Views: 256

Answers (2)

Andomar
Andomar

Reputation: 238296

Your SQL says: for each row in videocodec, select all rows in audiocodec. That will give 10x10 or 100 rows. It's equivalent to:

select  *
from    videocodec
cross join
        audiocodec

You probably mean to retrieve both audio and video codecs. You can do that with a union:

select  codecname
from    videocodec
union
select  codecname
from    audiocodec

Upvotes: 4

mwittrock
mwittrock

Reputation: 2931

You're getting back all combinations of rows from both tables, also known as the Cartesian product. 10 x 10 = 100.

Upvotes: 1

Related Questions