Home > Blockchain >  Inner join with multiple conditions on one column
Inner join with multiple conditions on one column

Time:12-13

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

id name tags
52 texas houston, dallas, austin
53 washington spokane, seattle
54 oregon portland, seaside

Table geofence_rates

id origin_id destination_id price
1 52 53 1200
2 53 54 700
3 54 52 900

Desired HTML Output from combining tables

origin id origin name origin tags destination id destination name destination tags price
52 texas houston, dallas, austin 53 washington spokane, seattle 1200
53 washington spokane, seattle 54 oregon portland, seaside 700
54 oregon portland, seaside 52 texas houston, dallas, austin 900

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.

  • Related