How do you only get data from the table with the foreign key using SQLite?

Question

I want to be able to get data from the table that owns the foreign key as a primary key, only using data in the WHERE clause from the other table.

I have tried an inner join and I am getting weird results. I am using https://sqliteonline.com to test this.

I have two tables, one called cars that has a name and ID. One that is called logged_data that has an ID, track, and car ID which is a foreign key using car.id.

I want to get all (distinct) car names that have logged data at a specific track.

These are the statements I tried:

CREATE TABLE cars (id INTEGER PRIMARY KEY NOT NULL, name TEXT UNIQUE NOT NULL DEFAULT 'Car1');
INSERT INTO cars DEFAULT VALUES;
INSERT OR IGNORE INTO cars (name) VALUES ("BMW");
INSERT OR IGNORE INTO cars (name) VALUES ("test");
CREATE TABLE logged_data (id INTEGER PRIMARY KEY NOT NULL, track TEXT NOT NULL, car_id INTEGER NOT NULL, FOREIGN KEY(car_id) REFERENCES cars(id) ON DELETE CASCADE);
INSERT INTO logged_data (track, car_id) VALUES ("track", (SELECT id FROM cars WHERE name = "Car1"));
INSERT INTO logged_data (track, car_id) VALUES ("track", (SELECT id FROM cars WHERE name = "Car1"));
INSERT INTO logged_data (track, car_id) VALUES ("track", (SELECT id FROM cars WHERE name = "BMW"));
INSERT INTO logged_data (track, car_id) VALUES ("track", (SELECT id FROM cars WHERE name = "BMW"));
INSERT INTO logged_data (track, car_id) VALUES ("track1", (SELECT id FROM cars WHERE name = "test"));
INSERT INTO logged_data (track, car_id) VALUES ("track1", (SELECT id FROM cars WHERE name = "test"));
SELECT DISTINCT name FROM cars INNER JOIN logged_data ON logged_data.car_id = cars.id WHERE track = "track";

This returns:

Car1
BMW
test

test should not be returned as it has not got logged data at track.

I tried selecting everything to see what the joined tables look like:

SELECT * FROM cars INNER JOIN logged_data ON logged_data.car_id = cars.id WHERE track = "track";

This just returns all the data for all tracks even though I only asked for track.

What is strange though, if I get distinct car names that have data logged at track1. It correctly only returns test. Is this the correct approach for what I want? Why am I seeing this different behaviour?

SELECT DISTINCT name FROM cars INNER JOIN logged_data ON logged_data.car_id = cars.id WHERE track = "track1";

This returns:

test

Answer 1

The secret is in double quotes: https://www.sqlite.org/lang_keywords.html

A string in double-quotes is an identifier. It is used primarily for cases where you have field name with a space inside or something similar.

So by having a field track and by doing track = "track" you comparing field track with itself.

When you trying to do track = "track1" - there is no object track1 so it is understood as a string literal. Quoting the documentation:

If a keyword in double quotes (ex: "key" or "glob") is used in a context where it cannot be resolved to an identifier but where a string literal is allowed, then the token is understood to be a string literal instead of an identifier.

The solution to your problem - use single quotes for string literals:

SELECT DISTINCT name 
FROM cars 
INNER JOIN logged_data ON logged_data.car_id = cars.id 
WHERE track = 'track';

As you can see, this is your very first query with corrected syntax for string.