Intersection of Two Tables in SQL

Question

Basically I need to create a new table that uses specific information from two other tables.

For example, I have a table called person with the elements person_id, first_name, last_name, gender, age, and fav_quote. I have a second table called department with the elements dept_id, dept_name, and building. I now need to create and intersection table with the person_id and dept_id elements included. And both must be the primary key (which I assume just means PRIMARY KEY (person_id, dept_id) command in my source).

CREATE TABLE person (

person_id INT(8) NOT NULL auto_increment,

first_name VARCHAR(25) NOT NULL,

last_name VARCHAR(25) NOT NULL,

gender VARCHAR(1),

age INT(8),

fav_quote TEXT,

PRIMARY KEY (person_id)

);

CREATE TABLE department (

dept_id INT(8) NOT NULL auto_increment,

dept_name VARCHAR(25) NOT NULL,

building VARCHAR(25) NOT NULL,

PRIMARY KEY (dept_id)

);

That is the code I have for the initial two tables I'm just not sure how to create an intersection and, having gone back over my notes, I can't find the instructions on how to write it.

Hi. What is an example query among the many you have seen in your intro to querying that is like what you want? Also, "intersection" doesn't mean anything here. Use enough words & sentences to actually say what you mean. Eg in terms of tables, rows, value for a column of a row, etc. How can you expect to reason & communicate re querying if you don't force yourself to say what you mean? If you expressed your question/goal/problem clearly you could google it find an answer. (Here, to this obvious basic faq, but also in general). — philipxy
This is all the instruction I have to go on, using that exact language "an intersection table", which I already said. And I have already googled it in many different terms. This is for a school project that I need to do and not only have I reviewed my own lessons and previous work, but I've consulted various explanatory sites as well. I apologize if it isn't clear to you, but that's exactly the struggle I'm having as well. — Sarah
You do not want "the intersection of two tables" as your title wrongly says; you want "an intersection table". Again, what is an example of one have you seen & how does that not help you? — philipxy

Mureinik Mureinik · Accepted Answer · 2018-11-06T07:04:45

You got the primary key part right. I'd add foreign keys to your existing table in order to prevent creating interactions with people or departments that don't exist:

CREATE TABLE person_department
    person_id INT(8) NOT NULL,
    dept_id INT(8) NOT NULL,
    PRIMARY KEY(person_id, dept_id),
    FOREIGN KEY(person_id) REFERENCES person(person_id),
    FOREIGN KEY(dept_id) REFERENCES department(dept_id)
)

Intersection of Two Tables in SQL

2 Answers