MySQL: Select Data from multiple Columns on Multiple conditions

Question

I have following tables:

Sender
---------
ID, name, type, mobile


Receiver
-----------
ID, name, type, mobile


Ports
-----------
ID, name, city, country


Shipment
-----------
ID, Sender_ID, Receiver_ID, From_Port_ID, To_Port_ID, Date, Status

I was trying to display shipment table contents in my webpage with names instead of IDs. I can fetch sender and receiver name by the following query:

SELECT sender.name AS Sender, receiver.name AS Receiver, shipment.id 
FROM sender,receiver,shipment
WHERE shipment.sender_ID=sender.ID AND shipment.receiver_ID=receiver.ID

How to fetch port names from ports table?

score 1 · Answer 1 · answered Mar 14 '14 at 18:19

1

Try:

SELECT sender.name as Sender, receiver.name as Receiver, shipment.id, ports.name AS Port 
FROM sender,receiver,shipment,ports 
WHERE shipment.sender_ID=sender.ID AND shipment.receiver_ID=receiver.ID AND ports.ID = shipment.From_Port_ID

answered Mar 14 '14 at 18:19

gorgi93

2,457
8
30
53

score 1 · Answer 2 · edited May 23 '17 at 12:27

You can do it joining twice with ports:

select sender.name as Sender, receiver.name as Receiver, 
      shipment.id, port1.name as OriginPort, port2.name as DestinationPort
from sender,receiver,shipment, ports port1, ports port2 
WHERE shipment.sender_ID=sender.ID 
      AND shipment.receiver_ID=receiver.ID
      AND shipment.From_Port_ID = port1.id
      AND shipment.To_Port_ID = port2.id

I also suggest you to change your query to use the explicit syntax for join (see Explicit vs implicit SQL joins for more information on this), as follows:

select sender.name as Sender, receiver.name as Receiver, 
      shipment.id, port1.name as OriginPort, port2.name as DestinationPort
from sender JOIN shipment ON sender.ID = shipment.sender_ID
        JOIN receiver ON shipment.receiver_ID=receiver.ID
        JOIN ports port1 ON shipment.From_Port_ID = port1.id
        JOIN ports port2 ON shipment.To_Port_ID = port2.id

Mohammed Joraid · Accepted Answer · 2014-03-14T18:56:46.257

You can use:

SELECT sender.name as Sender, receiver.name as Receiver, ports_a.name as FromPort,
ports_b.name as ToPort

FROM Shipment as shipment

JOIN Sender
ON sender.ID= shipment.sender_ID

JOIN  Receiver  as receiver
ON receiver.ID= shipment.receiver_ID

JOIN Ports as ports_a
on ports_a.ID= shipment.From_Port_ID 

JOIN Ports as ports_b
on ports_b.ID= shipment.To_Port_ID

First line includes SELECT, as what you did, just put all the info you want to fetch.

The second part is, to specify from where you want to get the info. FROM will be the from table, which is the main table that connects all other tables.

Then that table, will be joined with other tables using the keyword JOIN. It's like connection table by having a common reference. This reference is the ID in Shipment table such as sender_ID.

For example sender_ID for will be matched with Sender.id from Sender table as in Sender.id #5 will be connected with Shipment.sender_ID #5.

Perfect, you guys are all awesome – Mudassar Khani Mar 14 '14 at 18:47 — Mudassar Khani, Mar 14 '14 at 18:47

score 0 · Answer 4 · answered Mar 14 '14 at 18:30

I HOPE IS THAT WHAT YOU WANT !!!!...

select 
 S.name as Sender
,R.name as Receiver
,SH.id
,PFROM.name
,PTO.name

from shipment AS SH

INNER JOIN PORTS AS PFROM ON PFROM.ID = SH.From_Port_ID
INNER JOIN PORTS AS PTO ON PTO.ID = SH.From_Port_ID
INNER JOIN SENDER AS S ON SH.sender_ID=S.ID 
INNER JOIN RECEIVER AS R ON SH.receiver_ID=R.ID

MySQL: Select Data from multiple Columns on Multiple conditions

4 Answers4