CODEWITHSUNDEEP
mysqlsqldatabase
Annabelle Green
Annabelle Green

Reputation: 13

Use a Table as a Variable in SQL

I have two tables classroom and computer and currently computer is a variable in the table classroom

CREATE TABLE classroom_tbl
(
   room_id        INT AUTO_INCREMENT PRIMARY KEY,
   teacher_name   VARCHAR(30),
   subject_name   VARCHAR(30),
   computer       VARCHAR(30)
);

and I want to make it so instead of being a VARCHAR in the classroom table the variable computer calls the computer table

CREATE TABLE computer_tbl
(
   computer_id       INT AUTO_INCREMENT PRIMARY KEY,
   computer_type     VARCHAR(30),
   computer_status   INT
);

Is there any way to do this? I've tried UNION and INNER JOIN but I always get an error that says that my columns are different sizes. Which makes sense because classroom is bigger than computer. Thanks for any help you can give!

Upvotes: 1

Views: 91

Answers (3)

Annabelle Green
Annabelle Green

Reputation: 13

If anyone comes across this, what I wanted to do was not possible. You can only reference other columns in other tables or create foriegn keys. Unfortunatly you cannot reference an entire table.

Upvotes: 0

user359040
user359040

Reputation:

Since a given classroom can have many computers in it, but a given computer can only be in one classroom at a time, it makes more sense to have classroom as a foreign key on the computer table, rather than vice versa:

CREATE TABLE classroom_tbl
(
   room_id        INT AUTO_INCREMENT PRIMARY KEY,
   teacher_name   VARCHAR(30),
   subject_name   VARCHAR(30)
); 

CREATE TABLE computer_tbl
(
   computer_id       INT AUTO_INCREMENT PRIMARY KEY,
   computer_type     VARCHAR(30),
   computer_status   INT,
   room_id           INT
);

To see which computers are in each room, try a query like:

select r.room_id, r.teacher_name, r.subject_name,
       c.computer_id, c.computer_type, c.computer_status
from classroom_tbl r
left join computer_tbl c on r.room_id = c.room_id

Upvotes: 0

Juned Ahsan
Juned Ahsan

Reputation: 68715

I believe you are new to SQL and have some experience in programming. SQL does not have variables like we do have in programming langauages such as C/C++/java. Rather SQL tables relate to each other with relationships such as foreign key relationship. You need to go through SQL tutorials for understanding more about relationships, here is a link to one of those:

http://www.functionx.com/sql/Lesson11.htm

In order to use the JOINS you need to have Primary-foreign key relationship between the two tables. You may have to create your table like this:

CREATE TABLE classroom_tbl
(
   room_id        INT AUTO_INCREMENT PRIMARY KEY,
   teacher_name   VARCHAR(30),
   subject_name   VARCHAR(30),
   computer_id    INT REFERENCES computer_tbl(computer_id)
);

Upvotes: 1

Related Questions