Issue with SQL subquery, getting null values-CodePudding

I have a query similar to given below. When I run the individual queries, they work but when ran together I am getting null values for the last part as given in the output table. am I doing something wrong?

WITH TRAIN AS 
(
SELECT
train.number AS Train#,
train.date AS Date
FROM train.db AS train
WHERE train.date = '2022-04-15'
),
COACH AS
(
SELECT
coach.number,
coach.train_nbr,
coach.train_dt
FROM coach.db as coach
WHERE coach.train_dt = '2022-04-15'
AND coach.train_nbr IN (SELECT DISTINCT train.number FROM TRAIN) 
),
SEAT AS 
(
seat.coach_nbr AS Coach#,
seat.number AS Seat#
FROM seat.db AS seat
WHERE seat.train_dt = '2022-04-15' 
AND seat.coach_nbr IN (SELECT DISTINCT coach.number FROM COACH)
)
SELECT
TRAIN.*,
SEAT.*
FROM TRAIN
    LEFT OUTER JOIN COACH ON TRAIN.number = COACH.train_nbr
    LEFT OUTER JOIN SEAT ON COACH.number = SEAT.coach_nbr

Output I am getting is attached. table

CodePudding user response：

try to use INNER JOIN, to understand the behavior of your sentence over your data, it seems to be a problem in your seccond LEFT OUTER JOIN as commented by O. Jones, looks like the values of your SEAT table doesn't match with the values in COACH. Overall is hard to know what are going without looking at your data.

CodePudding user response：

The query seems overly complex. I don't understand the need to use CTEs here (with clause). For example the filtering for seats that have coach_nbr value in COACH table is something that is automatically done for you when using left join.

It looks as if the data model is flawed. I don't understand how the seats in a coach can differ by date so I suppose train_dt should not be a column in your SEAT table.

Anyhow I suppose this query should produce the required output for the tables as they are now:

select
    train.number as Train#,
    train.date as Date,
    seat.coach_nbr as Coach#,
    seat.number as Seat#
from
    train.db as train
    left join coach.db as coach on coach.train_nbr = train.number and coach.train_dt = train.date
    left join seat.db as seat on seat.coach_nbr = coach.number and seat.train_dt = train.date
where
    train.date = '2022-04-15'
;