CODEWITHSUNDEEP
sqlitejoinsql-like
Edward Tanguay
Edward Tanguay

Reputation: 193352

Why does SQLite's LIKE% not work in a JOIN?

Using the Chinook test database, the following SQL statement works in SQLite:

SELECT * FROM playlist WHERE Name LIKE '%the%'

Yet if I use JOINs:

SELECT * FROM playlist AS pl 
JOIN playlisttrack AS plt ON pl.PlaylistId=plt.PlaylistId 
JOIN track AS t ON plt.TrackId=t.TrackId WHERE pl.Name LIKE '%the%'

SQLite fails on the WHERE statement, although MySQL works fine:

enter image description here

What makes SQLite fail here?

Upvotes: 1

Views: 1177

Answers (2)

Matthias Gelbmann
Matthias Gelbmann

Reputation: 341

The LIKE seems to work, as you get the same result.

The problem seems to be multiple columns with the name "Name" in the three tables. I would avoid "SELECT *" and select the columns you need instead, using aliases to make clear what is what.

Upvotes: 1

maxwell2022
maxwell2022

Reputation: 2855

Are you sure you have playlisttrack and track for your playlist?

Replace JOIN by LEFT JOIN to get all playlist even if they don't have playlisttrack or track

Upvotes: 1

Related Questions