Crossing a primary key with 2 foreign keys from the same table?

Asked

Viewed 64 times

0

I was studying some SQL and came across the following problem:

I have 2 tables one called Order and another Locations, follow the fields below:

ORDER

id | data_creation | id_origin | id_destination | operator |

LOCATIONS

id | city_name | state_name

I need a Join crossing the ID field (from the Location table) with the id_source and id_destination fields (from the order table)

in the end would need something of the kind below:

ID order | operator | state name

How can I make that cross?

Attempts made by me:

inner join location on location.id = order.id_origem and location.id = id_destino
sql postgresql select join

1 answer

1

The table LOCATIONS will participate twice in the merge once in the role of origin and again in the role of destination.

SELECT * FROM ORDER INNER JOIN LOCATIONS origem ON (ORDER.id_origem = origem.id)
                    INNER JOIN LOCATIONS destino ON (ORDER.id_destino = destino.id)

replace * with the list of desired fields (I didn’t understand your example ID order | operadora | nome do estado, referred to in nome do estado? Origin or destination?).

  • in this case being 2 Inner Join, will not return only the records that exist at the same in the two tables?

  • Yes, both id_origem how much id_destino will need to exist in the table LOCATIONS. If both, or one of them, may not exist, just replace the INNER JOIN for LEFT OUTER JOIN. But the author of the question did not clarify this point.

  • @anonimo, the name of the state would be, Paraná, São Paulo etc. That way you showed in the final select, I will have to pull origin and destination, however, in the table LOCATION, I have the city ID, name of the city and the state to which it belongs, and in the order table I have only Id_origin and id_destination, I want to see if it is possible in the final select to bring me only the state of the source and destination cities, so I need a Join(if possible) that uses a primary key with two foreign ones, for in the final select I have only one column bringing the states.

  • @anonimo, this crossing that you did, I have also performed, but with it I bring in the final select the state of my destination city and the state of my origin city, I wish I did not need to bring these two columns, I wanted to bring the states of origin and destinations in one column only.

  • Post a desired output example to understand what you want.

  • Table LOCATIONS: id | name_city | name_state, table ORDER id | dat_creation | id_origin | id_destination | carrier |. Output: Order ID | Operator | Status

  • Example with data please (not just field names). What does it mean Estado on his way out?

  • Output: Order ID | Operator | Status following the order of these fields the output would see 001 Cometa PR 002 Garcia SC 003 Clickbus SP

  • Your table ORDER has the fields id_origem and id_destino that, I presume, refer to the field id table LOCATIONS.&#I’ve asked, but it seems you don’t understand, the content of Estado refers to the row in the table LOCATIONS pointed by id_origem or id_destino? Continue believing that the query I posted contains these, and several other fields of your tables, just put in the selection list the fields that interest you. Ending this discussion here because this type of discussion is not the aim of this list, but can be done in chat.

Show 4 more comments

Browser other questions tagged sql postgresql select join

You are not signed in. Login or sign up in order to post.