PostgreSQL: Error in left join

Question

I am trying to join my master table to some sub-tables in PostgreSQL in a single select query. I am getting a syntax error and I have the feeling I am making a terrible mistake or doing something which is not allowed. The code:

Select
id,
length,
other_stuff
from my_table tbl1
Left join
(
Select
id,
height
from my_table2 tbl2) tbl2 using (id)
left join
-- I get syntax error here
(
With a as (select id from some_table),
     b as (Select value from other_table)
Select id, value from a, b) tbl3 using (id)
order by tbl1.id

Can we use WITH clause in left joins sub or nested queries and Is there a better way to do this?

UPDATE1

Well, I would like to add some more details. I have three select queries like this (having unique ID) and I want to join them based on ID.

Query1:

With a as (Select id, my_other records... from postgres_table1)
     b as (select id, my_records... from postgres_table2)
     c as (select id, my_record.. from postgres_table3, b)
     Select
           id,
           my_records
     from a left join c on some_condtion_with_a
     order by 1

Second query:

Select
      id, my_records
from
    (
    multiple_sub_queries_by_getting_records_from_c
    )

Third Query:

With d as (select id, records.. from b),
     e as (select id, records.. from d),
     f as (select id, records.. from e)
select
      id,
      records..
from f

I tried to join them using left join. The first two queries were joined successfully. While, joining third query I got the syntax error. Maybe, I am complicating things thus I asked is there a better way to do it.

Select id, value from a, b creates a cross join between a and b (so a cross join between some_table and other_table). Is that really what you want? — user330315
– user330315, Commented Jun 14, 2017 at 13:37

user330315 · Accepted Answer · 2017-06-14 13:40:00Z

1

You are over complicating things. There is no need to use a derived table to outer join my_table2. And there is no need for a CTE plus a derived table to join the tbl3 alias:

Select id,
       length,
       other_stuff
from my_table tbl1
  Left join my_table2 tbl2 using (id) 
  left join (
    select st.id, ot.value
    from some_table st
       cross join other_table ot
  ) tbl3 using (id)
order by tbl1.id;

This assumes that the cross join you create with Select id, value from a, b is intended.

answered Jun 14, 2017 at 13:40

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

khajlk Over a year ago

Please see edit. I have tried to describe my scenario in more details. In summary, I want to join the result of second and third queries to the first but there are multiple sub-queries and derived tables in my structure.

Oto Shavadze · Accepted Answer · 2017-06-14 13:34:36Z

1

Not tested, but I think you need this. try:

with a as (select id from some_table),
b as (Select value from other_table)
Select
id,
length,
other_stuff
from my_table tbl1
Left join
(
    Select
    id,
    height
    from my_table2 tbl2
) 
tbl2 using (id)
left join
(
    Select id, value from a, b
) 
tbl3 using (id)
order by tbl1.id

answered Jun 14, 2017 at 13:34

Oto Shavadze

43.3k56 gold badges169 silver badges248 bronze badges

Comments

Caius Jard · Accepted Answer · 2017-06-14 13:41:28Z

1

I've only ever seen/used WITH in the following format:

WITH 
  temptablename(columns) as (query),
  temptablename2(columns) as (query),
  ...
  temptablenameX(columns) as (query)

SELECT ...

i.e. they come first

You'll probably find it easier to write queries if you use indentation to describe nesting levels. I like to make my SELECT FROM WHERE GROUPBY ORDERBY at one indent level, and then tablename INNER JOIN ON etc more indented:

SELECT
  column
FROM
  table
  INNER JOIN
  (
     SELECT subcolumn FROM subtable WHERE subclause
  ) myalias
  ON
    table.id = myalias.whatever
WHERE
  blah

Organising your indents every time you nest down a layer really helps. By making everything that is "a table or a block of data like a table (i.e. a subquery)" indented the same amount you can easily see the notional order that the DB should retrieve

Move your WITHs to the top of the statement, you will still use the alias names in place in the sub sub query of course

Looking at your query, there isn't much point in your subqueries.. You don't do any grouping or particularly complex processing of the data, you just select an ID and another column and then join it in. Your query will be simpler if you don't do this:

SELECT
  column
FROM
  table
  INNER JOIN
  (
     SELECT subcolumn FROM subtable WHERE subclause
  ) myalias
  ON
    table.id = myalias.whatever
WHERE
  blah

Instead, do this:

SELECT
  column
FROM
  table
  INNER JOIN
  subtable
  ON
    table.id = subtable.id
WHERE
  blah

edited Jun 14, 2017 at 13:41

answered Jun 14, 2017 at 13:35

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

1 Comment

khajlk Over a year ago

Thanks a lot for your details. I have added the structure of my three queries which I want to join. There are multiple nested queries and group by in my code.

Caius Jard · Accepted Answer · 2017-06-15 10:32:30Z

Re your updated requirements, following the same pattern.

look for --my comments

With a as (Select id, my_other records... from postgres_table1)
     b as (select id, my_records... from postgres_table2)
     c as (select id, my_record.. from postgres_table3, b)
     d as (select id, records.. from b),
     e as (select id, records.. from d),
     f as (select id, records.. from e)

SELECT * FROM
(
    --your first 
    Select
      id,
      my_records
    from a left join c on some_condtion_with_a

) Q1
LEFT OUTER JOIN
(
    --your second
    Select
      id, my_records
    from
    (
      multiple_sub_queries_by_getting_records_from_c
    )

) Q2
ON Q1.XXXX = Q2.XXXX --fill this in !!!!!!!!!!!!!!!!!!!

LEFT OUTER JOIN
(
    --your third
    select
      id,
      records..
    from f

) Q3
ON QX.XXXXX = Q3.XXXX --fill this in !!!!!!!!!!!!!!!!!!!

It'll work, but it might not be the prettiest or most necessary SQL arrangement. As both i and HWNN have said, you can rewrite a lot of these queries where you're just doing some simple selecting in your WITH.. But likely that theyre simple enough that the database optimizer can also see this and rerwite the query for you when it runs it

Just remember to code clearly, and lay your indentation out nicely to stop it tunring into a massive, unmaintainable, undebuggable spaghetti mess

Collectives™ on Stack Overflow

PostgreSQL: Error in left join

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related