Select from multiple tables

[Former Member]

Hey,

I have a table MEASUREMENT (DATE, VALUE)  and one EVENT (EVENT-ID, DATE), now I want to select the value of a measurement one day before an event into a separate table MEASUREMENT_BEFORE_EVENT. Therefore I using following SQL statement.

INSERT INTO "MEASUREMENT_BEFORE_EVENT" ("VALUE","DATE")
SELECT "VALUE","DATE" FROM "MEASUREMENT"
WHERE "DATE" IN (SELECT ADD_DAYS("EVENT-DATE",-1) FROM "EVENT" ORDER BY "EVENT_DATE" ASC);

Now I would like to extend this and insert also the EVENT-ID into MEASUREMENT_BEFORE_EVENT

INSERT INTO "MEASUREMENT_BEFORE_EVENT" ("VALUE","DATE","EVENT-ID")
SELECT "VALUE","DATE" (SELECT "DATE" FROM "EVENT") FROM "MEASUREMENT"
WHERE "DATE" IN (SELECT ADD_DAYS("EVENT-DATE",-1) FROM "EVENT" ORDER BY "EVENT_DATE" ASC);

When I do like this then I get

ERROR: single-row query returns more than one row

Is there a possibility to do that?

[SergioG_TX · Accepted solution]

Matthias,

the issue is on your inner query select "DATE" from "EVENT" you will need to do an inner join between your EVENT table and your MEASUREMENT table so that you can get multiple records in your select statement. once you get your select statement working then you can perform your insert operation.

hope this helps

[Former Member · Accepted solution]

Thx Sergio,

But the problem is when I do a INNER JOIN I have only the MEASUREMENT from the day of the event but not before a EVENT.

I would need to use the ADD_DAY function in INNER JOIN

[muthuram_shanmugavel2 · Accepted solution]

Hi Matthias,

You can use ADD_DAYS Function in Inner Join Query.

Please try this. I hope It will work.

SELECT A."M_VALUE", A."M_DATE", B."E_EVENTID"

FROM MEASUREMENT A

INNER JOIN EVENT B

ON A."M_DATE" = ADD_DAYS(B."E_DATE", -1)

Best Regards,

Muthu

[Former Member · Accepted solution]

Thank you very much, now it works well.

I tried it before to use ADD_DAYS Function in Inner Join Query but I always got the same day back.