Postgresql: UNION ALL on two queries using "with <name> as"?-CodePudding

I have this query:

with active_cars as (
    select c.id
    from car c
    inner join dealership d on c.dealer_id = d.id and d.active=true and d.ownerId=${userId}
)
select cd.*
from car_details cd
inner join active_cars ac on cd.car_id = ac.id
where cd.ratings=5;

Then I have query:

with ports_active_cars as (
    select c.id
    from car c
    inner join ports p on c.port_id = p.id and p.active=true and p.ownerId=${userId}
)
select cd.*
from car_details cd
inner join ports_active_cars pac on cd.car_id = pac.id

I'd like to know how I can combine the result of the two queries into one, so I get one result including all car_details records.

I tried this:

with active_cars as (
    select c.id
    from car c
    inner join dealership d on c.dealer_id = d.id and d.active=true and d.ownerId=${userId}
)
select cd.*
from car_details cd
inner join active_cars ac on cd.car_id = ac.id
where cd.ratings=5

union all

with ports_active_cars as (
    select c.id
    from car c
    inner join ports p on c.port_id = p.id and p.active=true and p.ownerId=${userId}
)
select cd.*
from car_details cd
inner join ports_active_cars pac on cd.car_id = pac.id;

but that is wrong, does not run.

Is there a way to combine the two into one result returning all rows of car_details?

CodePudding user response：

You need to define both CTEs at the start of the statement:

with active_cars as (
    select c.id
    from car c
    inner join dealership d
        on c.dealer_id = d.id and d.active = true and d.ownerId = ${userId}
),
ports_active_cars as (
    select c.id
    from car c
    inner join ports p
        on c.port_id = p.id and p.active=true and p.ownerId = ${userId}
)

select cd.*
from car_details cd
inner join active_cars ac on cd.car_id = ac.id
where cd.ratings = 5

union all

select cd.*
from car_details cd
inner join ports_active_cars pac on cd.car_id = pac.id;