Problem with query and 2 FK - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with query and 2 FK

Author

Topic

Giocanni
Starting Member

3 Posts

Posted - 2008-03-05 : 07:53:15

Hello, I have a problem with a query... and 2 tables

I want to associate each travel with a departure and an arrival port. I keep all the ports in another table

TravelsTable - Travel_ID(pk) ; DeparturePort(fk) ; ArrivalPort(fk)
PortTable - Port_ID(pk) ; PortLocation

my two foreign keys in the TravelsTable are linked to the Port_ID in the PortTable... so I have records like this in the Tables

Travel_ID=1 DeparturePort = 1 ArrivalPort = 4
Travel_ID=2 DeparturePort = 3 ArrivalPort = 2
Travel_ID=3 DeparturePort = 4 ArrivalPort = 5

Port Table
Port_ID=1 PortLocation= London
Port_ID=2 PortLocation= Le Havre
Port_ID=3 PortLocation= New York
Port_ID=4 PortLocation= La Rochelle
Port_ID=5 PortLocation= Zeebrugge

----------------

What I need is to have a list with the Name of the departure/arrival ports... I need something like this

Travel_ID Departure Arrival
1 London La Rochelle
2 New York Le Havre
....

Can you help me writing this query, in Linq or sql, or both? I read about joins but I can't seem to put things together... I'm not even sure is the way to do it

Thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-05 : 07:57:37

Select c.Travel_IDa.Departureport, b.ArrivalPort
FROM TravelsTable c inner join Port_ID a on c.Travel_ID = a.DeparturePort
inner join Port_ID b on c.Travel_ID = b.ArrivalPort

Poor planning on your part does not constitute an emergency on my part.

Giocanni
Starting Member

3 Posts

Posted - 2008-03-05 : 09:06:25

great! this seems exactly what I needed thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-05 : 09:22:51

Giocanni, next time you have a question pleasepost in this format
such as you provide proper and accurate sample data

--Prepare sample data
DECLARE	@Travels TABLE
	(
		TravelID INT,
		DeparturePortID INT,
		ArrivalPortID INT
	)

INSERT	@Travels
	(
		TravelID,
		DeparturePortID,
		ArrivalPortID
	)
SELECT	1, 1, 4 UNION ALL
SELECT	2, 3, 2 UNION ALL
SELECT	3, 4, 5

DECLARE	@Ports TABLE
	(
		PortID INT,
		PortLocation VARCHAR(40)
	)

INSERT	@Ports
	(
		PortID,
		PortLocation
	)
SELECT	1, 'London' UNION ALL
SELECT	2, 'Le Havre' UNION ALL
SELECT	3, 'New York' UNION ALL
SELECT	4, 'La Rochelle' UNION ALL
SELECT	5, 'Zeebrugge'

If you do this, we can easily provide you with a query like this

SELECT		t.TravelID,
		d.PortLocation AS Departure,
		a.PortLocation AS Arrival
FROM		@Travels AS t
INNER JOIN	@Ports AS d ON d.PortID = t.DeparturePortID
INNER JOIN	@Ports AS a ON a.PortID = t.ArrivalPortID

E 12°55'05.25"
N 56°04'39.16"

Giocanni
Starting Member

3 Posts

Posted - 2008-03-05 : 10:57:11

Ok Mr. Peso I'll try to compile it that way next time. Or Can I generate it from the database?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources