What is wrong with this code that is causing Syntax Error in PostgreSQL 9.3

Question

Below is a piece of SQL code I wrote. I want to merge two records in to one record where the first record domain is shown as "From source" and the second record's domain become "To Domain". There will be more filtering I need to do but why is this simple statement not working?

I get an error "Syntax error at end of input"

*SELECT 
   "ID" , "Time",  "Source Domain", "To Domain" From
   (SELECT "RecordID" As "ID","UTCTimestamp" As "Time","Domain" As "Source Domain" FROM public."Traffic - Mobile")T1
   Inner Join Lateral
   (SELECT "Domain" As "To Domain" FROM public."Traffic - Mobile" where "RespondentID"="T1"."RespondentID" )T2*

Thanks in advance

PostgreSQL version 9.3

score 1 · Accepted Answer · 2018-01-03T10:11:51.897

1

I think you are overcomplicating this. A simple self join of the table is enough. You don't need a derived table to rename a column

SELECT "ID", "Time", t1."Domain" as "Source Domain", t2."Domain" as "To Domain" 
from public."Traffic - Mobile" as t1
  join public."Traffic - Mobile" as t2 on t2."RespondentID" = t1."RespondentID";

edited Jan 03 '18 at 10:11

answered Jan 03 '18 at 07:29

I am getting the following error. This is useful. What will i miss from lateral join? ERROR: missing FROM-clause entry for table "T1" SQL state: 42P01 – Jan 03 '18 at 10:06
@user2315860: sorry, copy & paste error. (In general you should avoid quoted identifiers like the plague) – Jan 03 '18 at 10:12

score 0 · Answer 2 · answered Jan 03 '18 at 07:18

I think you still need an ON clause since it's an INNER JOIN. If you didn't want to specify an ON clause, I think you could use CROSS JOIN instead.

E.g.

SELECT 
   "ID" , "Time",  "Source Domain", "To Domain" From
   (SELECT "RecordID" As "ID","UTCTimestamp" As "Time","Domain" As "Source Domain"
          FROM public."Traffic - Mobile")T1
   Inner Join Lateral
   (SELECT "Domain" As "To Domain" FROM public."Traffic - Mobile"
           where "RespondentID"="T1"."RespondentID" )T2
    ON true

I'm getting this error ERROR: missing FROM-clause entry for table "T1" SQL state: 42P01 — , Jan 03 '18 at 09:59

What is wrong with this code that is causing Syntax Error in PostgreSQL 9.3

2 Answers2