Query to pull data from three tables

Databases
1

I have these tables

create table devices (
   device_id INT NOT NULL AUTO_INCREMENT,
   rack_id INT NOT NULL,
   orientation CHAR(1) DEFAULT '1',
   beginning_slot DECIMAL(3,1) NOT NULL,
   ending_slot DECIMAL(3,1) NOT NULL,
   device VARCHAR(100) NOT NULL,
   width INT DEFAULT NULL,
   beginning_x INT DEFAULT NULL,
   display CHAR(1) DEFAULT '1',
   updated_by VARCHAR(25) DEFAULT 'Luke Utnoski',
   updated_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   FOREIGN KEY ( rack_id ) REFERENCES racks (rack_id),
   PRIMARY KEY ( device_id )
);

create table users (
   user_id SMALLINT NOT NULL AUTO_INCREMENT,
   name VARCHAR(50) NOT NULL,
   email VARCHAR(50) NOT NULL,   
   password VARCHAR(50) NOT NULL,
   created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   logged_in TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY ( user_id )
);

create table device_history (
   device_id INT NOT NULL,
   user_id SMALLINT NOT NULL,
   updated_to_orientation CHAR(1) DEFAULT '1',
   updated_to_beginning_slot DECIMAL(3,1) NOT NULL,
   updated_to_ending_slot DECIMAL(3,1) NOT NULL,
   updated_to_device VARCHAR(100) NOT NULL,
   updated_to_width INT DEFAULT NULL,
   updated_to_beginning_x INT DEFAULT NULL,
   updated_to_display CHAR(1) DEFAULT '1',
   updated_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   FOREIGN KEY ( device_id ) REFERENCES devices (device_id),
   FOREIGN KEY ( user_id ) REFERENCES users (user_id)
);

and am trying to pull data from each given a device_id.
I have to make sure that the insert statement for the device_history table looks like

INSERT INTO device_history VALUES (3, 4, 3, 4, 'Device 3',);

how would I form a SELECT query to pull both the device data and the user data?

2

like this –

SELECT d.device_id
     , d.rack_id
     , d.device
     , dh.updated_to_beginning_slot
     , dh.updated_date
     , u.user_id
     , u.name
  FROM devices AS d
INNER
  JOIN device_history AS dh
    ON dh.device_id = d.device_id
INNER
  JOIN users AS u
    ON u.user_id = dh.user_id
 WHERE d.device_id = 3

by the way, your INSERT statement isn’t going to work, you’ve got fewer values than columns

3

I thought I didn’t-have to name the columns in an INSERT if its got a defaut

4

no, that’s wrong

you don’t have to supply a value if a column has a default

but the syntax has to be observed – you can skip naming the columns only if you provide a value for all of them

how long have you been doing SQL? :wink:

5

How would it know which column to apply the values to? As your third column has a default, how would MySQL know whether the third value you provide should go into that third column and override the default, or go into the next column that doesn’t have one?

6

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