CODEWITHSUNDEEP
mysqlsqldatabaserelational-database
ReynierPM
ReynierPM

Reputation: 18680

Get tp.address_from and tp.address_to from travel_point table

I'm building a view for get tp.address_from and tp.address_to but I can't get this works. This is the query I'm working on:

SELECT
    `tp`.`address_from` AS `address_from`,
    `tp`.`address_to` AS `address_to`,
    `bk`.`firstname` AS `firstname`
FROM
    (
        `book` `bk`
        LEFT JOIN `travel_point` `tp` ON(
            (
                (`bk`.`tp_from` = `tp`.`id`)
                AND(`bk`.`tp_to` = `tp`.`id`)
            )
        )
    )

But it's not working since I get always a set of empty results as picture below shows:

enter image description here

Between book and travel_point tables exists the same relation twice: tp_from is a FK on book and also tp_to is a FK and both points to travel_point.id maybe here is the problem and I don't see it yet. Anyway, I leave the SQL schema in this link because is a bit large, apologies for that but and I provide the same data I've for testing purpose.

What I'm doing wrong?

PS: You can take a look to data on this images I leave and as I said data is shared

book_table travel_point

Upvotes: 0

Views: 29

Answers (2)

philipxy
philipxy

Reputation: 15148

LEFT JOIN specifically returns a row for every left hand table row; if there's no ON match then NULL is returned in the columns unique to the right hand table. For your select that returns a row for every book row, with NULL addresses when id differs from tp_from or tp_to. If that's not what you want then don't use LEFT JOIN. You don't say what you do want so it's hard to advise you otherwise. Presumably just use JOIN.

Aside from your NULL problem:

Guessing at what your tables mean, your query (after the LEFT is dropped) lists first names and corresponding endpoint addresses of round trip bookings. It seems unlikely that a travel point has a from address and a to address. Is there really just a travel point address? (But maybe a travel point is a link in an itinerary that knows its preceding/from and following/to address.) It also seems unlikely that you would be listing the same travel point's address twice. For first names and endpoint addresses of bookings you need to join with travel_point twice, once for each endpoint. (As in another answer for a different query.)

SELECT tpf.address AS address_from, tpt.address AS address_to, bk.firstname
FROM book bk
JOIN travel_point tpf ON bk.tp_from = tpf.id
JOIN travel_point tpt ON bk.tp_to = tpt.id

Upvotes: 1

Naktibalda
Naktibalda

Reputation: 14110

bk.tp_from = tp.id AND bk.tp_to = tp.id
looks like impossible condition, because it is only satisfied when tp_from = tp_to.

if you want to get both points as separate rows in a result set, change join condition to
bk.tp_from = tp.id OR bk.tp_to = tp.id

If you want to get 2 travel points related to booking, you have to join travel_point table twice.

SELECT *
FROM book AS bk
JOIN travel_point AS tp1 ON `bk`.`tp_from` = `tp1`.`id`
JOIN travel_point AS tp2 ON `bk`.`tp_to` = `tp2`.`id`

Upvotes: 1

Related Questions