Categories: php, mysql, sql, database, join

Joining the same table twice with different values

1 answer

I'm trying to replace the value of "transfers.pickup_areas_group_id" and "transfers.drop_areas_group_id" with the values from the table "areas_group", using IDs

I'm using this query:

SELECT  transfers.id AS transfer_id,  transfers.name AS transfer_name,   transfers.pickup_areas_group_id AS transfer_pickup_areas_group_id, transfers.drop_areas_group_id AS transfer_drop_areas_group_id,  transfers_pricing.vehicle_id AS vehicle_id,  transfers_pricing.date_start AS date_start,  transfers_pricing.date_end AS date_end,  transfers_pricing.price AS price  FROM transfers INNER JOIN transfers_pricing ON transfers_pricing.transfer_id = transfers.id 

I tried an extra INNER JOIN to replace the first value "transfers.pickup_areas_group_id", but I couldn't find a way to replace the second one "transfers.drop_areas_group_id"

I tried this query:

SELECT  transfers.id AS transfer_id,  transfers.name AS transfer_name,   transfers.pickup_areas_group_id AS transfer_pickup_areas_group_id, areas_group.area_id AS pickup_area_ids, transfers.drop_areas_group_id AS transfer_drop_areas_group_id,  transfers_pricing.vehicle_id AS vehicle_id,  transfers_pricing.date_start AS date_start,  transfers_pricing.date_end AS date_end,  transfers_pricing.price AS price  FROM transfers INNER JOIN transfers_pricing ON transfers_pricing.transfer_id = transfers.id INNER JOIN areas_group ON areas_group.id = transfers.pickup_areas_group_id 

Thank you,

All answers to this question, which has the identifier 61218999

The best answer:

Basically, you need another join on areas_group; to disambiguate the two references to the same table, you need to use a table alias.

Actually, it is a good practice to use table aliases for all tables that come into play in the query: this makes the query shorter to read and write.

SELECT      t.id AS transfer_id,      t.name AS transfer_name,       t.pickup_areas_group_id AS transfer_pickup_areas_group_id,     ag1.area_id AS pickup_area_ids,     t.drop_areas_group_id AS transfer_drop_areas_group_id,      ag2.area_id AS drop_area_ids     tp.vehicle_id AS vehicle_id,      tp.date_start AS date_start,      tp.date_end AS date_end,      tp.price AS price  FROM transfers t INNER JOIN transfers_pricing tp ON tp.transfer_id = t.id INNER JOIN areas_group ag1 ON ag1.id = t.pickup_areas_group_id INNER JOIN areas_group ag2 ON ag2.id = t.drop_areas_group_id 

Last questions

how do i remove the switch on my home screen?
how to edit the JS date and time to update atuomatically?
How to utilize data stored in a multidimensional array
Powermockito not mocking URL constructor in URI.toURL() method
Android Bluetooth LE Scanner only scans when phone's Location is turned on in some devices
docker wordpress container can't connect to mysql container
How can I declare a number in java that is more than 64-bits? [duplicate]
Optaplanner solutionClass entityCollectionProperty should never return null error when simple JSON object passed to controller
Anylogic, get the time a pedestrain is in a queue
How do I fix this syntax issue with my .flex file?
Optimizing query in PHP
How to find the highest number of a column and print two columns of that row in R?
Ideas on “Error: Type com.google.firebase.iid.zzav is referenced as an interface from com.google.firebase.messaging.zzd”?
JCIFS SmbFile.exists() and SmbFile.isDirectory() return false when it exists and I can listFiles()
PHP total order
Laravel booking system design
neural net - undefined column selected
How to indicate y axis does not start from 0 in ggplot?
Fragments in backStack
Spinner how to change the data