1

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";

enter image description here

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

1 Answer 1

2

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.

1
  • Thank you for the help!
    – av4625
    Feb 1 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.