Juan Diego Juan Diego - 1 year ago 36
SQL Question

Creating a view for 3 tables with a little complex relationships

So I have 3 tables, "ruta", "track_ruta", "punto_ruta"
ruta has a relationship of one to many to track_ruta and another relationship of one to many to punto_ruta.
track_ruta has also a relationship of one to many to punto_ruta.
ruta means route in Spanish by the way. So long story short a Route has tracks and tracks have points. There can be points without tracks that belong to a Route. That is why it is the way it is.
I need to retrieve and do searches according to dates on the points and I need to look for points of a route.
I had this as an sql sentence

select DISTINCT p.*
from PuntoRuta p, Ruta r, TrackRuta t
where ((r.codigo=ROUTEID
AND t.ruta.codigo=r.codigo AND p.trackRuta.codigo=t.codigo)
OR p.ruta=ROUTEID)

ROUTEID is replacing the id on my code

This worked. I dont like using DISTINCT I know it is costly and it worked for while. Until a USER had like million points on his route and it takes for ever to search even when I am using limits.
I have not had to handle complex db relations in ages so I am really rusty.

I am very fuzzy on how to transform this into a view. I was using JOINS to do this

FROM punto_ruta AS p
JOIN track_ruta AS t
ON p.id_track_ruta=t.id_track_ruta
JOIN ruta r
ON r.id_ruta=t.id_ruta
right OUTER JOIN ruta r1
ON r1.id_ruta=p.id_ruta

I tried also FULL OUTER JOIN but in both cases it is just adding all my Routes at the end. I am no able to add the points that belong only to a route and not to a track.

Should I combine 2 queries? Like adding this

FROM punto_ruta AS p
JOIN ruta AS r
On r.id_ruta=p.id_ruta

to the query before?

Answer Source

Let independed points have null in track_id

r(id, ...)
t(id, r_id, ...)
p(id, r_id, t_id, ...)

select p.*
  from p
  where p.r_id = ROUTEID
    and p.t_id is null
union all
select p.*
  from t left join p on t.id = p.t_id
  where t.r_id = ROUTEID