Select multiple columns, tables from a specific id and timestamp-CodePudding

I am trying to select multiple columns, tables for a specific id and timestamp. For example, I am trying to retrieve data from the reservation, trip, and session table for id=fff222 from 02/14/2022-02/15/2022 between in UTC timestamp. I tried this implementation however it gave me more rows than when I manually search each table. Where did I went wrong Updated implementation:

SELECT reservation.reason, reservation.state, reservation.inserted_at, From reservation JOIN session Join trip on
trip.sessionId=trip.sessionId and trip.sessionId=session.sessionId
(here something missing)
WHERE trip.sessionId='fff222' ORDER BY session.inserted_at between '2022-02-14 00:00:00' and '2022-02-15 23:59:59'

Any suggesstion to get the same exact amount of rows when searching each table manually

CodePudding user response：

You need to join the tables. Try this

SELECT 
    reservation.reason,
    reservation.state, 
    reservation.inserted_at,
    session.reason,
    session.state,
    session.inserted_at,
    trip.reason, 
    trip.state, 
    trip.inserted_at 
FROM 
    reservation JOIN session JOIN trip
ON 
    trip.sessionId = reservation.sessionId AND 
    trip.sessionId = session.sessionId
WHERE 
    trip.sessionId='fff222'
ORDER BY 
    vtq.session.inserted_at

CodePudding user response：

Adjust your query to:

use the join keyword for joins
add join conditions
change the order of joins to the natural "bottom-up" order
add aliases and formatting for readability

Try this:

select
  r.reason,
  r.state,
  r.inserted_at,
  s.reason,
  s.state,
  s.inserted_at,
  t.reason,
  t.state,
  t.inserted_at
from trip t 
join session s on s.id = t.sessionId
join reservation r on r.sessionId = t.sessionId
where t.sessionId = 'fff222'
and s.inserted_at between '2022-02-14 00:00:00' and '2022-02-15 23:59:59'
order by s.inserted_at