Multi table sql query

Question

I need to create a query with multiple tables for a project for school, but I'm not really good at this.

I've got 2 tables.

The first is a table with trajects:

departure_time (time)
arrival_time (time)
departure_id (int)
arrival_id (int)

The second table holds the names of the locations:

location_id (int)
name (varchar)

I would need a query that gets the departure_time, arrival_time and the names of the departure place and the arrival place.

Alex K. · Accepted Answer · 2011-05-07 12:51:01Z

1

With an inner join;

select
  departure_time,
  arrival_time,
  depart.name,
  arrive.name
from trajects 
  inner join locations depart on (depart.location_id = trajects.departure_id)
  inner join locations arrive on (arrive.location_id = trajects.arrival_id)

answered May 7, 2011 at 12:51

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeff Swensen · Accepted Answer · 2011-05-07 12:46:10Z

1

SELECT 
  t.departure_time, 
  t.arrival_time, 
  d.name as 'DeparturePlace', 
  a.name as 'ArrivalPlace'
FROM 
  Trajects t, Locations d, Locations a 
WHERE 
  t.departure_id = d.location_id AND 
  t.arrival_id = a.location_id

answered May 7, 2011 at 12:46

Jeff Swensen

3,57331 silver badges52 bronze badges

Comments

geekchic · Accepted Answer · 2011-05-07 12:45:00Z

0

What you'd need to do is an SQL JOIN on the departure_id and the arrival_id onto the second table. Details and examples here.

answered May 7, 2011 at 12:45

geekchic

1,5661 gold badge11 silver badges21 bronze badges

3 Comments

Jerodev Over a year ago

Thank you, but the trouble is that i need 1 row from the first table and two rows from the second table.

geekchic Over a year ago

A join like the one I'm describing would be able to achieve this. Didn't just want to write a quer for you as this sounds homeworky.

Jerodev Over a year ago

Don't worry, the project is much bigger than this.

Collectives™ on Stack Overflow

Multi table sql query

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related