Column is ambiguous?

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

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.

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