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

HHaller
September 19, 2023
135 views
3 votes
3 Answers

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: postgresql sql

Answers

- VALONSO
- September 10, 2023 at 4:18 am
- 0 votes
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.

- JoopEggen
- September 10, 2023 at 4:39 am
- 0 votes
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.

- Abdulaziz
- September 10, 2023 at 1:24 pm
- 0 votes
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.