I have two tables
create table racks (
rack_id INT NOT NULL AUTO_INCREMENT,
location VARCHAR(100) NOT NULL,
elevation VARCHAR(100) NULL,
title VARCHAR(100) NOT NULL,
power_panel VARCHAR(100) NOT NULL,
circuit_breaker VARCHAR(10) NOT NULL,
number_of_slots INT NOT NULL,
display CHAR(1) DEFAULT '1',
PRIMARY KEY ( rack_id )
);
create table rack_history (
rack_id INT NOT NULL,
updated_to_location VARCHAR(100) NOT NULL,
updated_to_elevation VARCHAR(100) NULL,
updated_to_title VARCHAR(100) NOT NULL,
updated_to_power_panel VARCHAR(100) NOT NULL,
updated_to_circuit_breaker VARCHAR(10) NOT NULL,
updated_to_number_of_slots INT NOT NULL,
updated_to_display CHAR(1) DEFAULT '1',
updated_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY ( rack_id ) REFERENCES racks (rack_id)
);
An a trying to pull data from both using an INNER JOIN
SELECT racks.title, rack_history.updated_to_location, rack_history.updated_to_elevation, rack_history.updated_to_title, rack_history.updated_to_power_panel, rack_history.updated_to_circuit_breaker, rack_history.updated_to_number_of_slots, rack_history.updated_date, rack_history.updated_to_display FROM racks INNER JOIN rack_history ON rack_history.rack_id = racks.rack_id WHERE rack_id = 1
but
what does that error mean?
You need to state which table rack_id
must equal 1
in.
As in: WHERE tableName.rack_id = 1
Both tables have a column named rack_id
, so it’s ambiguous.
2 Likes
thanks, changed the query to
``
SELECT users.name, rack_history.updated_to_location, rack_history.updated_to_elevation, rack_history.updated_to_title, rack_history.updated_to_power_panel, rack_history.updated_to_circuit_breaker, rack_history.updated_to_number_of_slots, rack_history.updated_date, rack_history.updated_to_display FROM rack_history INNER JOIN users ON rack_history.user_id = users.user_id WHERE rack_id = 1
igor_g
May 31, 2019, 10:03pm
4
SELECT users.name, rack_history.*
FROM rack_history
INNER JOIN users
ON rack_history.user_id = users.user_id
WHERE rack_history.rack_id = 1
Look at @SamA74 comment.
r937
June 1, 2019, 1:28am
5
wait… you changed this –
SELECT ...
FROM racks
INNER
JOIN rack_history
ON rack_history.rack_id = racks.rack_id
WHERE rack_id = 1
to this –
SELECT ...
FROM rack_history
INNER
JOIN users
ON rack_history.user_id = users.user_id
WHERE rack_id = 1
that’s… that’s… that’s a pretty novel way of fixing an “ambiguous column” error – change the query to a different one!!!
not only that but you changed the rack_history table to include a new column, user_id???
man, you are good
system
Closed
August 31, 2019, 8:31am
6
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.