Reputation: 105
I'm new to data modeling, so please excuse the newbie questions:
How do I make a reference twice in 2 columns, to the same table? If I just made the reference once I would link them together by calling the column LocationID. E.g. I have a table called Locations, and a table called Tours:
Locations: ID (pk), Country, State, Street address, etc
Tours: ID (pk), Start location (how do i make this reference?), End location (and this one?), locations visited, description, cost, date, etc
Upvotes: 2
Views: 76
Reputation: 92845
Do you mean something like this?
CREATE TABLE locations
(
id INT NOT NULL,
country VARCHAR(64),
state VARCHAR(64),
street_address VARCHAR(64),
PRIMARY KEY(ID)
);
CREATE TABLE tours
(
id INT NOT NULL,
start_location INT NOT NULL,
end_location INT NOT NULL,
description VARCHAR(255),
cost DECIMAL(19,2),
date DATE,
PRIMARY KEY(ID),
FOREIGN KEY (start_location) REFERENCES locations (id),
FOREIGN KEY (end_location) REFERENCES locations (id)
);
Here is SQLFiddle demo
Upvotes: 2
Reputation: 5522
Give the table name aliases and then reference them with their new names as if they were separate tables, such as:
SELECT
*
FROM
TOURS
JOIN
LOCATIONS START_LOCATION ON
START_LOCATION.LOCATION_ID = TOURS.START_LOCATION
JOIN
LOCATIONS END_LOCATION ON
END_LOCATION.LOCATION_ID = TOURS.END_LOCATION
Upvotes: 0