I have two tables and I want to connect them and get three columns (name and license_plate and minute)

user_19161372 0
Viewed 25

I have two tables and I want to connect them and get three columns (name and license_plate and minute). I tried to run this SQL code:

SELECT name, license_plate 
FROM people
JOIN bakery_security_logs AS bsl ON bsl.license_plate = people.license_plate 
WHERE month = 7 
  AND day = 28 
  AND year = 2021 
  AND hour = 10;

I get an error:

Parse error near line 12: ambiguous column name: license_plate

When I only specify the name in the SELECT, I get the names, but I also want the license_plate and minute

These are the tables:

CREATE TABLE people 
(
    id INTEGER,
    name TEXT,
    phone_number TEXT,
    passport_number INTEGER,
    license_plate TEXT,
    PRIMARY KEY(id)
);

CREATE TABLE bakery_security_logs 
(
    id INTEGER,
    year INTEGER,
    month INTEGER,
    day INTEGER,
    hour INTEGER,
    minute INTEGER,
    activity TEXT,
    license_plate TEXT,
    PRIMARY KEY(id)
)
sql
1 Answers

Below query should solve your problem, your select query does not know from which table the column license_plate should be picked up and hence the error.

SELECT people.name, people.license_plate 
FROM people
JOIN bakery_security_logs AS bsl ON bsl.license_plate = people.license_plate 
WHERE month = 7 
  AND day = 28 
  AND year = 2021 
  AND hour = 10;
user_71140920
Related