Help with a join

I have 2 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 contents (
   entity_id INT NOT NULL AUTO_INCREMENT,
   rack_id INT DEFAULT 0,
   location VARCHAR(100) NOT NULL,
   entity_type CHAR(1) DEFAULT '0',
   entity_x_pos INT,
   entity_y_pos INT,
   entity_width INT DEFAULT 50,
   entity_height INT DEFAULT 100,
   entity_text VARCHAR(50) DEFAULT NULL,
   entity_secondary_text VARCHAR(50) DEFAULT NULL,
   display CHAR(1) DEFAULT '1',
   PRIMARY KEY (entity_id),
   FOREIGN KEY ( rack_id ) REFERENCES racks (rack_id)
);

Im trying to display a bunch of data from the contents table and only 1 thing from the racks table…
Whats the error

SELECT contents.entity_x_pos, 
        contents.entity_y_pos, 
        contents.entity_width, 
        contents.entity_height, 
        contents.enity_text, 
        contents.entity_secondary_text,
        racks.title 
FROM contents 
INNER JOIN racks ON racks.location = contents.location 
WHERE location = 'Watch Floor'

How can I get those 7 values if I can get location?

Which location are you trying to compare to watch floor?

Oh, does the foreign key go in there?

ON racks.racks_id = contents.rack_id WHERE..

that was it, thanks

SELECT contents.entity_x_pos, 
        contents.entity_y_pos, 
        contents.entity_width, 
        contents.entity_height, 
        contents.enity_text, 
        contents.entity_secondary_text,
        racks.title 
FROM contents 
INNER JOIN racks ON racks.location = contents.location 
WHERE racks.location = 'Watch Floor'

Try the above query else you can even try Subquery as below:

SELECT entity_x_pos, 
        entity_y_pos, 
        entity_width, 
        entity_height, 
        enity_text, 
        entity_secondary_text,
        title 
FROM (select 
       contents.entity_x_pos, 
        contents.entity_y_pos, 
        contents.entity_width, 
        contents.entity_height, 
        contents.enity_text, 
        contents.entity_secondary_text,
        racks.titlecontents from contents
INNER JOIN racks ON racks.location = contents.location ) as a
WHERE a.location = 'Watch Floor'
1 Like

nope, that’s gonna fail, because the a table doesn’t have a location column

also, i think it was already confirmed that the join should be made on the PK-FK link (post above yours)

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.