Help with a SQL query with a couple of foreign keys.

Question

I have the following tables structure:

Shipment (has IDDestination as foreign key) -> Destination (has IDCity as foreign key) -> City (has IDCountry as foreign key).

I want to give my stored procedure a Country ID and have it return all of the shipments. How would I do this with pure SQL syntax? I could do this trivially with an ORM, but I'm forcing myself to learn this.

Answer 1

You can use INNER JOINs to link all your tables together.

CREATE GetShipments

@CountryID int

AS

SELECT *
FROM Shipments
INNER JOIN Destination ON Shipment.IDDestination = Destination.ID
INNER JOIN Cities ON Destination.IDCity = Cities.ID
WHERE Cities.IDCountry = @CountryID

Answer 2

SELECT s.*
FROM City c INNER JOIN (
  Shipment s INNER JOIN Destination d ON s.IDDestination = d.ID)
  ON c.ID = d.IDCity
WHERE c.IDCountry = @IDCountry

Answer 3

Like this maybe...

select * from Shipment as s
inner join Destination as d on s.IDDestination = d.IDDestination
inner join City as c on d.IDCountry = c.IDCountry
where c.IDCountry = @CountryID

Answer 4

SELECT S.* 
FROM Shipment S
JOIN Destination D on S.IDDestination  = D.ID
JOIN City C on D.IDCity = C.ID
WHERE IDCountry = @IDCountry

Answer 5

For a visual demonstration of various JOINs, see this link.

Assuming @idcountry is an integer data type, use:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE get_shipments_for_countryid 
   @idcountry INT
AS
BEGIN

   SELECT DISTINCT 
          s.*
     FROM SHIPMENT s
     JOIN DESTINATION d ON d.id = s.iddestination
     JOIN CITY c ON c.id = d.idcity 
    WHERE c.idcountry = @idcountry
END
GO

Change the parameter data type, if not an INT, to whatever is appropriate.

The DISTINCT is necessary if any of the tables are many-to-many, or one-to-many...