How to return items of a certain column just once when using LEFT JOIN

Hi,
I’m trying to return a result from a query that contains an item with a certain id just once while combining tables with a LEFT JOIN. Without success so far.

This is the query I have now:

SELECT *
FROM veloroutestops
JOIN veloroutes
ON veloroutestops.veloroute_id = veloroutes.id
LEFT JOIN trainstops
ON veloroutestops.destination_id = trainstops.destination_id
JOIN destinations
ON veloroutestops.destination_id = destinations.id
WHERE veloroute_id IN ('$arr')
ORDER BY veloroutestops.stop_number

What’s causing trouble are these lines:

LEFT JOIN trainstops
ON veloroutestops.destination_id = trainstops.destination_id

I don’t want items with the same id from the veloroutestops table in the result.
What I want from this part of the query is this: I want to know whether an item with destination_id from the table veloroutestops has a corresponding entry in the table trainstops or not.
When I use a LEFT JOIN like I currently do, there will be an extra entry for every match in the trainstops table which causes duplicates. If I use INNER JOIN, the item from the veloroutestops table won’t be returned at all.

This is the design of my database:

Is there a way to return just one item from this part of the query?

Hmm struggling to wrap my head around this one but just to check, is it necessary to JOIN the columns from trainstops or are you looking to filter out entire rows from velostops if they don’t have a corresponding entry.

My first though would be this is going to be filtering with WHERE using NULLs or using IN or EXISTS if filtering rows entirely.

1 Like

Hi, thanks for taking a look :slight_smile:
I don’t want to filter out any lines that I have until that part of the query.
The background is: There are veloroutestops that have access to a train station and those that don’t.
I want to return each veloroute stop exactly once containing the information if there is a train station or not.
I could still have a separate query for that, but if I could do that already with this query, that would be a shortcut.

1 Like

I might still be barking up the wrong tree and apologies if that’s the case but if you’re just after a column that says “yes, there is a trainstop at this veloroutestop”, true/false style then perhaps CASE or COALESCE in the query would do.

Something along the lines of-

SELECT veloroutestops.*, COALESCE(trainstop.stop_number, 0) FROM ...
-- It doesn't have to be stop number
-- for custom e.g. "yes" / "no" CASE may be better

Obviously that still uses a join (which could matter for very large tables or repeated queries) but you’d only be returning the existing veloroutestop columns and an additional one for “is there a trainstop” at this stop.

1 Like

Thanks again!
I’m new to SQL so it might be very easy and just me not getting it…
I didn’t know the COALESCE function. Some research indicates that I can customize the output of an extra column (which I do want), but not the amount of rows returned after the JOIN function, is that correct?

Given this example:

The JOINing of these tables will be on destination_id. Both tables can hold multiple rows with the same destination_id though (different routes share destinations, at a train station can be different trains).

Therefore if I do an INNER JOIN, the veloroutestops without a matching destination_id in the trainstops table will be dropped, which I don’t want:

But if I use a left JOIN with veloroutestops being the left table, I’ll have veloroutestops duplicates:

I want to return the veloroutestops table with the rows unaltered (other than what the WHERE clause does), just with an extra column:

Do you think that can be achieved with COALESCE?

1 Like

I kinda like SQL but I don’t use it enough to be particularly good at it either.

I think I get you now though, not COALESCE then :sweat_smile:. COALESCE seems to me to closer to logical short-circuiting (a or b or c) as it exhibits fall through behaviour. I was under the impression you’d be left with a set of NULLs from your join which it normally handles quite nicely (more convenient than case can be).

Perhaps some kind of grouping on veloroutestops such that one column is basically made unique again, I assume the primary key would do? Probably CASE rather than coalesce for the trainstop true/false though.

I feel like there should also be a subquery which would net you a similar true/false column but it’s not coming to me and I might be talking nonsense.

1 Like

Did you try grouping by id, destination_id, veloroute_id, stop_number, train stop?

2 Likes

Yes, that works! Thank you!

SELECT veloroutestops.id, veloroutestops.stop_number, veloroute_id, destinations.id AS destination_id, dest_name, lat, lon, trainstops.destination_id AS trainstop, 
	CASE
      WHEN trainstops.destination_id IS NULL THEN 'false'
      ELSE 'true'
  	END
FROM veloroutestops
JOIN veloroutes
ON veloroutestops.veloroute_id = veloroutes.id
LEFT JOIN trainstops
ON veloroutestops.destination_id = trainstops.destination_id
INNER JOIN destinations
ON veloroutestops.destination_id = destinations.id
GROUP BY id
HAVING veloroute_id IN (14)
ORDER BY veloroutestops.stop_number

Thanks @coffeencake for also pointing me in that direction!

2 Likes

Depending on your familiarity with derived tables, one option might be the use of Distinct within a derived table. I use Microsoft SQL Server (previously Oracle), and we use the isnull function to replace null values with a more meaningful result (I’m not sure which variant of SQL you are using, so the 2nd line below may need adjusting, or be best as a case statement if a similar function doesn’t exist). I’m also not sure depending on variants of SQL whether true/false are automatically recognized as a boolean variable, so if this throws errors, I would simply store them as strings using quotation marks.

select veloroutestops.*,   
isnull(dest_distinct.trainstop, false) 
from veloroutestops
left join 
(select distinct 
destination_id,
true AS trainstop
from trainstops
) dest_distinct on veloroutestops.destination_id=dest_distinct.destination_id

Hoping I followed this all correctly, but happy to add more explanation on this answer if it’s no aligning with where you’re starting from.

2 Likes

Hi @bit6031689647
thank you for taking a look and your suggestion. Since the previous solution (with a slight change on the output of the column name) works perfectly for me, I already moved on.
But I am currently working with two queries for another fetch for the trainstops because the WITH clause isn’t known to my MySQL / phpMyAdmin version (temporarily on MAMP for development: 5.7.34 - MySQL) this might become interesting again – as it looks like you’re creating a temp table, right?
When I paste the query I get an error:

#1582 - Incorrect parameter count in the call to native function ‘isnull’

You anticipated this might happen due to different SQL versions, but I’m struggling to investigate this any further at the moment as the other solution also does the trick and I’m focussing on front end dev. Maybe I’ll be coming back to you when I try to refactor the other fetches if I may. Thanks again!

@mirja_t No problem - thought you might have moved forward, but that the value might be useful for others as well.

You are correct that I was creating a temp table. As an alternative to isnull, coalesce could be useful here. I don’t have much experience with coalesce since isnull is intuitive to me as a function name. As I understand it coalesce is a more general function that will work across more SQL languages. It also has a benefit of being able to take more than 1 argument. I think of the difference as isnull is equivalent to an if/else statement, whereas coalesce allows for if/else-if/…/else, which can be more useful with more complex data structures.

Good luck with your other projects, and the rest of your front-end dev; I’m currently working at the same skills as part of the full stack program, and really enjoying it.

1 Like