CODEWITHSUNDEEP
sqlpostgresql
James
James

Reputation: 375

Rewrite union query to a single query

I wondered if it was possible to write this as one query:

SELECT * FROM
(SELECT  "markets".* 
  FROM "markets" 
  INNER JOIN "positions" ON "positions"."id" = "markets"."position_id" 
  INNER JOIN "players" ON "players"."id" = "positions"."player_id" 
  INNER JOIN "other_players" ON "other_players"."id" = "players"."other_player_id" 
  WHERE (markets.updated_at > '2021-01-10 11:50:14.136015') 
  AND "markets"."on_feed" = true
  AND "markets"."deleted_at" IS NULL

  UNION

  SELECT  "markets".* 
  FROM "markets" 
  WHERE (markets.updated_at > '2021-01-10 11:50:14.136015') 
  AND "markets"."on_feed" = true 
  AND "markets"."deleted_at" IS NOT NULL) results
ORDER BY results.updated_at DESC

There's a lot of overlap in the two queries. In the first one we want all markets that have those associations in place. For the 2nd query we don't really care if the associations are there or not.

Upvotes: 0

Views: 458

Answers (2)

Marko
Marko

Reputation: 988

  SELECT * FROM "markets" 
  LEFT JOIN "positions" ON "positions"."id" = "markets"."position_id" 
  LEFT JOIN "players" ON "players"."id" = "positions"."player_id" 
  LEFT JOIN "other_players" ON "other_players"."id" = "players"."other_player_id" 
  WHERE markets.updated_at > '2021-01-10 11:50:14.136015'
  AND "markets"."on_feed" = true
  AND ("markets"."deleted_at" IS NOT NULL 
  OR "positions"."id" IS NOT NULL AND "players"."id" IS NOT NULL 
      AND "other_players"."id" IS NOT NULL)
  ORDER BY markets.updated_at DESC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Use EXISTS instead:

SELECT m.* 
FROM "markets"  m
WHERE m.updated_at > '2021-01-10 11:50:14.136015' AND
      markets."on_feed" = true AND
      (m."deleted_at" IS NOT NULL OR
       EXISTS (SELECT 1
               FROM "positions" p JOIN 
                    "players" pl
                    ON pl."id" = p."player_id" JOIN
                    "other_players" op
                    ON op."id" = p."other_player_id" 
               WHERE p."id" = m."position_id" 
              )
      )
ORDER BY m.updated_at DESC

Upvotes: 0

Related Questions