I suspect that you haven’t used @Relation correctly.

The following, albeit it including the parent Favorite, works:-

data class FavoriteRoute(
    @Embedded
    val favorite: Favorite,
    @Relation(entity = Airport::class, entityColumn = "iata_code", parentColumn = "departure_iata_code")
    val departure: Airport,
    @Relation(entity = Airport::class, entityColumn = "iata_code", parentColumn = "destination_iata_code")
    val destinatation: Airport
)

Demo

Using the above, your classes, DAO’s for insertion of some data and the DAO:-

@Transaction
@Query("SELECT * FROM favorite")
fun getFavoriteRoute(): List<FavoriteRoute>

• i.e. all FavoriteRoutes

An then the activity code:-

    db = TheDatabase.getInstance(this)
    dao = db.getAllDAOs()

    dao.insert(Airport(1,"iata_1","A1",100))
    dao.insert(Airport(2,"iata_2","A2",200))
    dao.insert(Airport(3,"iata_3","A3",300))
    dao.insert(Airport(4,"iata_4","A4",400))

    dao.insert(Favorite(100,"iata_3","iata_4"))
    dao.insert(Favorite(200,"iata_1","iata_2"))
    dao.insert(Favorite(300,"iata_2","iata_3"))

    val favs=dao.getFavoriteRoute()


    for (fr in dao.getFavoriteRoute()) {
        Log.d("DBINFO","FR ID is ${fr.favorite!!.id} FR_FROM is ${fr.favorite.departureCode} FR_TO is ${fr.favorite.destinationCode}" +
                "ntAP_FROM ID is ${fr.departure!!.id} AP_FROM CDE is ${fr.departure.iataCode} AP_FROM name is ${fr.departure.name} AP_FROM pass = ${fr.departure.passengers}" +
                "ntAP_TO ID is ${fr.destinatation!!.id} AP_TO CDE is ${fr.destinatation.iataCode} AP_TO name is ${fr.destinatation.name} AP_TO pass = ${fr.destinatation.passengers}")
    }
}

Results in the log including:-

D/DBINFO: FR ID is 100 FR_FROM is iata_3 FR_TO is iata_4
        AP_FROM ID is 3 AP_FROM CDE is iata_3 AP_FROM name is A3 AP_FROM pass = 300
        AP_TO ID is 4 AP_TO CDE is iata_4 AP_TO name is A4 AP_TO pass = 400
D/DBINFO: FR ID is 200 FR_FROM is iata_1 FR_TO is iata_2
        AP_FROM ID is 1 AP_FROM CDE is iata_1 AP_FROM name is A1 AP_FROM pass = 100
        AP_TO ID is 2 AP_TO CDE is iata_2 AP_TO name is A2 AP_TO pass = 200
D/DBINFO: FR ID is 300 FR_FROM is iata_2 FR_TO is iata_3
        AP_FROM ID is 2 AP_FROM CDE is iata_2 AP_FROM name is A2 AP_FROM pass = 200
        AP_TO ID is 3 AP_TO CDE is iata_3 AP_TO name is A3 AP_TO pass = 300

• as expected (i.e. 3-4, 1-2 and 2-3)

You mention the issue of ambiguous names. Using @Relation (which requires the parent for the relationship to be built) then Room circumvents any such issue as it does not use a JOIN but instead, when extracting the data for the relation executes a query to get ALL the related data (as you say as the iata_code is unique then just the single related data).

• As a single object is specified, as opposed to List, in the relationship, even if many rows were extracted, just 1 would be used.
  • perhaps note the very simple query just accessing the Favorite, Room does the rest.
  • saying that sometimes using @Relation and it’s get ALL can be problematic (e.g. if trying to filter the related data).

As such each Airport (dep/dest) will be built by an independent underlying. query.