MySQL join two columns on a single column

Question

I have 2 tables:

1. table_name: user_tracking
   With columns: id, bill_no, container_type, origin_id, destination_id

2. table_name: sea_ports
   With columns: id, bill_no, port_name

I want to write a single query to get the origin port_name and the destination port_name.

my query is :

select a.container_type, 
       b.port_name as origin_port
from user_tracking a 
left join sea_ports b 
on a.bill_no = b.bill_no 
where a.bill_number = '$bill_no'

How do I join the two columns origin_id and destination_id on the same field id from the table sea_ports to get two different outputs?

Answer 1

You need to join the table sea_ports twice so you can get the port_name for each origin and destination. And One more thing, I guess, you need need to use INNER JOIN rather than LEFT JOIN because there will always be destination and origin right? :D

SELECT  a.ID,
        a.bill_no,
        a.container_type,
        b.port_name AS Origin_name,
        c.port_name AS Destination_name
FROM    user_tracking a
        INNER JOIN sea_ports b
            ON a.origin_id = b.id
        INNER JOIN sea_ports c
            ON a.destination_id = c.id
WHERE   a.bill_number = '$bill_no'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

• How to prevent SQL injection in PHP?