Using two columns with the same foreign key

I have a database, which has 5 tables, but I have problem. In the shipment table, there are two columns, source and destination, which are both foreign key references to the route table, but when I select a record from the shipment table it will only show the same routename for both.

Here is the code:

SELECT fare, commission, driver,shipment._date, routename, 开发者_Go百科vehiclenumber, productname, source, destination, routename,ownername
FROM route, shipment, product, vehicle,owner
WHERE vehicle.vehicleid = shipment.vehicle
AND shipment.source
and vehicle.owner=owner.ownerid
AND shipment.destination = route.routeid
AND shipment.product = product.productid
AND vehicle.vehiclenumber =  'nk-234'
ORDER BY _date
LIMIT 0 , 30

To connect a record in one table (one shipment) to more than one record in another table (two routes) you would ideally use explicit JOINs against the route table twice (or more, for however many links you happen to need).

Here's a quick modification to your query that demonstrates usage. Pay particular attention to the two routename columns in the SELECT and the two JOINs to the route table in the FROM:

SELECT fare,
       commission,
       driver,
       shipment._date,
       RS.routename,    <-- field from first join
       vehiclenumber,
       productname,
       source,
       destination,
       RD.routename,    <-- field from second join
       ownername

FROM shipment
JOIN route RS ON RS.routeID = shipment.source       <-- join 1; source
JOIN route RD ON RD.routeID = shipment.destination, <-- join 2; destination
     product,
     vehicle,
     owner

WHERE vehicle.vehicleid = shipment.vehicle
  AND vehicle.owner = owner.ownerid
  AND shipment.product = product.productid
  AND vehicle.vehiclenumber =  'nk-234'

ORDER BY _date

LIMIT 0 , 30

Obviously, this might not match your needs exactly, because I don't know what tables the other selected fields are from to be able to make sure they are all accounted for...

This is because you select the fields from one row in shipment, in orer to actually get any effect from your foreign keys you need to use a few joins

http://dev.mysql.com/doc/refman/5.5/en/join.html

Using the old-style (non-preferred) notation, you can write:

SELECT s.fare, s.commission, s.driver, s._date, v.vehiclenumber, p.productname,
       s.source, s.destination, rs.routename, rd.routename, o.ownername
  FROM route AS rs, route AS rd, shipment AS s, product AS p, vehicle AS v, owner AS o
 WHERE s.vehicleid = s.vehicle
   AND s.source = rs.routeid
   AND v.owner = o.ownerid
   AND s.destination = rd.routeid
   AND s.product = p.productid
   AND v.vehiclenumber =  'nk-234'
 ORDER BY _date
 LIMIT 0, 30

I had to guess which table(s) contain the fare, commission and driver information; I assumed they are all in the shipment table.

Starting a column name with an underscore is not strictly allowed in standard SQL, though most DBMS do allow it. It looks ugly, though; why not 'ship_date' as the column name?

It is better to use the explicit join notation, not least because there is less chance of forgetting a join condition:

SELECT s.fare, s.commission, s.driver, s._date, v.vehiclenumber, p.productname,
       s.source, s.destination, rs.routename, rd.routename, o.ownername
  FROM shipment AS s
  JOIN route    AS rs ON s.source      = rs.routeid
  JOIN route    AS rd ON s.destination = rs.routeid
  JOIN vehicle  AS v  ON s.vehicleid   = s.vehicle
  JOIN owner    AS o  ON v.owner       = o.ownerid
  JOIN product  AS p  ON s.product     = p.productid
 WHERE v.vehiclenumber = 'nk-234'
 ORDER BY s._date
 LIMIT 0, 30