CODEWITHSUNDEEP
sqldatabasedatabase-designrelational-databasedata-modeling
Bez_almighty
Bez_almighty

Reputation: 105

How do I reference table B rows twice from table A?

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

Answers (2)

peterm
peterm

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

Lock
Lock

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

Related Questions