CODEWITHSUNDEEP
sqljoin
evade
evade

Reputation: 139

SQL Select from table twice

I am trying to select from the same table twice within SQL.

I have a POLICIES table that has an index (NEXTPOLICYID) that refers to itself.

I need to compare the current premium with the estimated premium.

How can I get a result that shows the following on the same result row?

t1 = Current t2 = Future

End result should be:

t1.POLICIES_ID | t1.WRITTEN_PREMIUM | t2.POLICIES_ID | t2.ESTIMATED_PREMIUM

This is what I have right now, and I am getting an error on my join statement, but I fear that is not my only problem.

SELECT 
    t1.POLICIES_ID, t1.WRITTEN_PREMIUM, t1.NEXTPOLICYID, t2.ESTIMATED_PREMIUM
FROM 
    POLICIES t1 JOIN
    POLICIES t2
    ON t1.NEXTPOLICYID = t2.POLICIES_ID

I am getting the following error:

Message: odbc_exec(): SQL error: [Rocket U2][UVODBC][1401233]Error ID: 29 Severity: ERROR Facility: FPSRVERR - Line 5, column 17 (around "JOIN"): Syntax error., SQL state S1000 in SQLExecDirect

This is an ODBC Connection to a uniVerse database, I have tested this with many other functions and it works fine. This error tells me it does not like something before the JOIN statement.

Thank you

Upvotes: 0

Views: 198

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Apart from the comma, the only other issues are:

  • You don't need to include t2.POLICIES_ID in the select list, because you have t1.NEXTPOLICYID
  • You might want to consider a left outer join, to keep policies that have no next policy.

The query might be:

 SELECT t1.POLICIES_ID, t1.WRITTEN_PREMIUM, t1.NEXTPOLICYID,
        t2.ESTIMATED_PREMIUM
 FROM POLICIES t1 JOIN
      POLICIES t2
      ON t1.NEXTPOLICYID = t2.POLICIES_ID;

Upvotes: 1

jpw
jpw

Reputation: 44881

This:

SELECT
    t1.POLICIES_ID,
    t1.WRITTEN_PREMIUM
    t1.NEXTPOLICYID,
    t2.ESTIMATED_PREMIUM,
    t2.POLICIES_ID
FROM 
    POLICIES t1,
    JOIN POLICIES t2 ON t1.NEXTPOLICYID = t2.POLICIES_ID

has some issues with commas in the wrong places and should be:

SELECT
    t1.POLICIES_ID,
    t1.WRITTEN_PREMIUM,
    t1.NEXTPOLICYID,
    t2.ESTIMATED_PREMIUM,
    t2.POLICIES_ID
FROM 
    POLICIES t1
    JOIN POLICIES t2 ON t1.NEXTPOLICYID = t2.POLICIES_ID

I'm guessing that's the reason for the error.

Upvotes: 1

Related Questions