Question 246996 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I "SELECT out" multiple differently-defined rows from a large join table without re-defining it for each row

I am using postgres.

What I have right now is like this:

SELECT <statement> FROM (
<MASSIVE JOIN STATEMENT>
)
UNION ALL
SELECT <different statement> FROM (
<SAME MASSIVE JOIN STATEMENT>
)

What I would really like is to have is a way to accomplish the same thing without having to put in the same massive join statement multiple times. Is there a way to accomplish this?

Thank You!!

I tried giving the table an alias and referencing the alias in the second SELECT statement, but it didn’t work. Postgres says the relation does not exist.

Tags:

3

Answers


  1. 0

    Given its the same source for both selects, have you tried using a CTE?

    e.g:

    WITH CTE AS (
  -- Your MASSIVE JOIN STATEMENT here
)

SELECT <statement> FROM CTE
UNION ALL
SELECT <different statement> FROM CTE;
    Login or Signup to reply.
  2. 0

    You can make a table VIEW for the massive SELECT.

    However inspect the massive SELECT. In a similar case I had an outer join that could be simplified by a GROUP BY. That would be lucky though.

    As you are doing … UNION ALL …, you probably have SELECT 1, … UNION ALL SELECT 2, … too. Maybe some of the joins are just for references.

    Not a very satisfactory answer but the question seems to indicate that a simplification should be possible rather than a super pivot table or stored procedure.

    Login or Signup to reply.
  3. 0

    Another way is by wrapping your original query in a subquery and then selecting from it in the UNION ALL part, you can get it working.

    SELECT <statement>
FROM (
  SELECT <columns>
  FROM (
    -- your massive join statement goes here
    -- you can also include any filters or conditions you need
    SELECT <columns>
    FROM <your_tables>
    WHERE <your_conditions>
  ) AS subquery1

  UNION ALL

  SELECT <different statement>
  FROM (
    -- the same massive join statement
    SELECT <columns>
    FROM <your_tables>
    WHERE <your_conditions>
  ) AS subquery2
) AS combined_query;

    This avoids repeating the massive join statement while achieving the desired outcome

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search