CODEWITHSUNDEEP
sqlsqliteinner-join
mwolf
mwolf

Reputation: 11

SQLite3 INNER JOIN with 3 tables

I'm trying to select from 3 tables with an INNER JOIN:

The tables:

CREATE TABLE tracks (
'track_id' INTEGER PRIMARY KEY NOT NULL,
'name' TEXT NOT NULL,
'length' REAL DEFAULT '0.00',
'city' TEXT
);

CREATE TABLE heats (
'heat_id' INTEGER PRIMARY KEY NOT NULL,
'track_id' INTEGER UNSIGNED NOT NULL,
'heat_pos' INTEGER UNSIGNED NOT NULL,
'day_pos' INTEGER UNSIGNED NOT NULL,
'type' TEXT NOT NULL DEFAULT 'training',
'average' REAL,
'date' TEXT,
'comment' TEXT,
FOREIGN KEY ('track_id') REFERENCES tracks ('track_id')
);

CREATE TABLE laps (
'lap_id' INTEGER PRIMARY KEY NOT NULL,
'heat_id' INTEGER UNSIGNED NOT NULL,
'laptime' REAL UNSIGNED NOT NULL,
FOREIGN KEY ('heat_id') REFERENCES heats ('heat_id')
);

When selecting information from 2 tables (laps and heats) it works like I expected:

select 
laps.lap_id, 
laps.laptime,
heats.heat_pos
from laps
inner join heats on laps.heat_id = heats.heat_id;

But now I want to select the corresponding tracknames from the track table:

select 
laps.lap_id, 
laps.laptime,
heats.heat_pos,
tracks.name
from laps, tracks, heats
inner join heats on laps.heat_id = heats.heat_id and
inner join heats on tracks.track_id = heats.track_id;

This gives me the following error:

ambiguous column name: heats.heat_pos

I'm completely lost, but I have a feeling it's just a small mistake. Anyone knows what I'm doing wrong?

Upvotes: 1

Views: 1040

Answers (2)

arpan shah
arpan shah

Reputation: 277

select 
laps.lap_id, 
laps.laptime,
heats.heat_pos,
tracks.name
from laps
inner join heats on laps.heat_id = heats.heat_id
inner join heats on tracks.track_id = heats.track_id
ORDER BY laps.lap_id

Upvotes: -1

Avitus
Avitus

Reputation: 15958

select 
laps.lap_id, 
laps.laptime,
heats.heat_pos,
tracks.name
from laps
inner join heats on laps.heat_id = heats.heat_id 
inner join tracks on tracks.track_id = heats.track_id;

Upvotes: 2

Related Questions