I am trying to combine two tables to display.

I have one table (geofence) which holds each region id, name and associated tags. The second table is all routes and prices the user has entered from the available entries in their geofence table.

Table geofence

Table geofence_rates

Desired HTML Output from combining tables

I would like to show all routes, the price and then the associated name and tags for each of the geofence ID's.

My current sql statement gets me the routes and price but will only show the origin name and tags based off the origin id. I am not sure how to also extract the destination name and tags.

SELECT geofence_rates.origin_id, geofence_rates.destination_id, 
        geofence_rates.price, geofence.id, geofence.name, geofence.tags
FROM geofence_rates 
    INNER JOIN geofence ON geofence_rates.origin_id = geofence.id

How I can run a single statement and get both the origin and destination name and tags. I understand the bolded portion of my statement is what is causing this, but I am unsure how to create two conditions.

CodePudding user response：

Your current sql statement gets you the routes and price only for the origin because you're matching the two tables on one single condition (the matching origin: geofence_rates.origin_id = geofence.id), yet at same time you're requiring information for destination too.

To solve this, you can apply two JOIN operations:

• former to get information on origin
• latter to get information on destination

separately.

SELECT orig.id      AS origin_id,
       orig.name    AS origin_name,
       orig.tags    AS origin_tags,
       dest.id      AS destination_id,
       dest.name    AS destination_name,
       dest.tags    AS destination_tags,
       rates.price
FROM       geofence_rates rates
INNER JOIN geofence       orig
        ON rates.origin_id = orig.id
INNER JOIN geofence dest
        ON rates.destination_id = dest.id
ORDER BY origin_id

Check the demo here.