sqlpostgresqllateral

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


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


Solution

  • 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";