The join request is different for one column
I would like the results of the second query to overlap the results of the first query:
SELECT "panel_restaurants_restaurant"."id",
"panel_restaurants_restaurant"."name",
"panel_restaurants_restaurant"."logo",
"panel_restaurants_restaurantfeatures"."currency" AS "currency",
ST_DistanceSphere(location, ST_GeomFromText('POINT(0.0 0.0)',4326)) AS "distance",
"panel_meals_meal"."id" AS "meal_id",
"panel_meals_meal"."status" AS "meal_status",
"panel_meals_meal"."available_count" AS "available_dishes",
"panel_meals_meal"."discount_price" AS "discount_price",
"panel_meals_meal"."normal_price" AS "normal_price",
"panel_meals_meal"."collection_from" AS "pickup_from",
"panel_meals_meal"."collection_to" AS "pickup_to",
"panel_meals_meal"."description" AS "meal_description"
FROM "panel_restaurants_restaurant"
INNER JOIN "panel_restaurants_restaurantfeatures" ON (
"panel_restaurants_restaurantfeatures"."restaurant_id" = "panel_restaurants_restaurant"."id")
LEFT OUTER JOIN "panel_meals_meal" ON ("panel_restaurants_restaurant"."id" = "panel_meals_meal"."restaurant_id"
AND "panel_meals_meal"."status" = 0
AND (
("panel_meals_meal"."collection_from" AT TIME ZONE 'Europe/Warsaw')::date = DATE 'today' OR
("panel_meals_meal"."collection_from" AT TIME ZONE 'Europe/Warsaw')::date = DATE 'tomorrow'
)
AND "panel_meals_meal"."collection_to" > '2017-07-29 19:33:47.992075+00:00'
AND "panel_meals_meal"."available_count" > 0)
WHERE "panel_restaurants_restaurant"."status" = 2
UNION
SELECT "panel_restaurants_restaurant"."id",
"panel_restaurants_restaurant"."name",
"panel_restaurants_restaurant"."logo",
"panel_restaurants_restaurantfeatures"."currency" AS "currency",
ST_DistanceSphere(location, ST_GeomFromText('POINT(0.0 0.0)',4326)) AS "distance",
"panel_meals_meal"."id" AS "meal_id",
"panel_meals_meal"."status" AS "meal_status",
"panel_meals_meal"."initial_count" AS "available_dishes",
"panel_meals_meal"."discount_price" AS "discount_price",
"panel_meals_meal"."normal_price" AS "normal_price",
"panel_meals_meal"."collection_from" AS "pickup_from",
"panel_meals_meal"."collection_to" AS "pickup_to",
"panel_meals_meal"."description" AS "meal_description"
FROM "panel_restaurants_restaurant"
INNER JOIN "panel_restaurants_restaurantfeatures" ON (
"panel_restaurants_restaurantfeatures"."restaurant_id" = "panel_restaurants_restaurant"."id")
LEFT OUTER JOIN "panel_meals_meal" ON (
"panel_restaurants_restaurant"."id" = "panel_meals_meal"."restaurant_id" AND
"panel_meals_meal"."status" = 0)
INNER JOIN "panel_meals_mealrepeater" ON (
"panel_meals_mealrepeater"."meal_id" = "panel_meals_meal"."id")
WHERE "panel_restaurants_restaurant"."status" = 2 AND "panel_meals_mealrepeater"."saturday" = true
ORDER BY distance ASC
For example, the first request may return a null value for that comes from the table panel_meals_meal
, but the second back something - in this situation I would have the same values for id
, name
, logo
, currency
, distance
and different values (zeros returned from the first request, and something
from another ) for everyone else.
So the question is, how do you make this UNION
distinguishable across a specific range of columns (actually one - is enough id
)?
source to share
You can do what you want with help FULL OUTER JOIN
instead UNION
and use COALESCE
to your advantage.
I'll simplify my script to focus on the part FULL OUTER JOIN
:
These are tables (think of them as the result of your first one SELECT
before whe UNION
, and the second one SELECT
after that UNION
):
CREATE TABLE table_a
(
id INTEGER NOT NULL PRIMARY KEY,
name TEXT,
logo TEXT
) ;
CREATE TABLE table_b
(
id INTEGER NOT NULL PRIMARY KEY,
name TEXT,
logo TEXT
) ;
This is the data we have:
INSERT INTO
table_a
(id, name, logo)
VALUES
(1, 'Name1-A', 'Logo1-A'),
(2, NULL, NULL),
(3, 'Name3-A', NULL),
(4, NULL, 'Logo4-A'),
(5, 'Name5-only-in-A', NULL);
INSERT INTO
table_b
(id, name, logo)
VALUES
(1, 'Name1-B', 'Logo1-B'),
(2, 'Name2-B', NULL),
(3, 'Name3-B', 'Logo3-B'),
(4, 'Name4-B', 'Logo4-B'),
(6, 'Name6-only-in-B', 'Logo6-B');
The query you are looking for is done by concatenation in such a way that you extract all rows from table_a
and table_b
. Then you use:
SELECT
id,
COALESCE(a.name, b.name) AS name,
COALESCE(a.logo, b.logo) AS logo
FROM
table_a AS a
FULL OUTER JOIN table_b AS b USING(id)
ORDER BY
id ;
id | name | logo -: | : -------------- | : ------ 1 | Name1-A | Logo1-A 2 | Name2-B | null 3 | Name3-A | Logo3-B 4 | Name4-B | Logo4-A 5 | Name5-only-in-A | null 6 | Name6-only-in-B | Logo6-B
dbfiddle here
In your case, replace table_a AS a
with your full first (SELECT ...) AS a
as well as b
. I assumed they id
are your primary keys.
Literature:
-
FULL OUTER JOIN
-
COALESCE
- FROM clause (find
USING
)
source to share
I did something similar using the WITH / CTE query :
WITH override_query AS (SELECT * FROM blah_blah JOIN blah_blah [etc]),
first_query AS (SELECT * FROM blah_blah JOIN blah_bluh [etc]
WHERE id NOT IN (SELECT id FROM override_query))
TABLE first_query UNION TABLE override_query
source to share
Usage for example DISTINCT ON
SELECT DISTINCT ON (maintenance_task_id)
maintenance_task_id,
execution_count
FROM (
SELECT
id maintenance_task_id,
0 execution_count
FROM maintenance_task
UNION
SELECT
mte1.maintenance_task_id,
count(*) execution_count
FROM maintenance_task_execution mte1
WHERE
mte1.ended_at IS NULL
GROUP BY mte1.maintenance_task_id
) AS t
ORDER BY
maintenance_task_id,
execution_count DESC
In this request:
-
UNION
combines the results of two queries. -
DISTINCT ON
selects one row from the top (based onORDER BY
) for each unique valuemaintenance_task_id
.
source to share