Question Re: Select from multiple tables in Technology Q&A

Select from multiple tables

Former Member — Tue, 01 Mar 2016 15:23:27 GMT

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?

Re: Select from multiple tables

SergioG_TX — Tue, 01 Mar 2016 15:53:18 GMT

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

Re: Select from multiple tables

Former Member — Wed, 02 Mar 2016 12:32:54 GMT

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

Re: Select from multiple tables

muthuram_shanmugavel2 — Thu, 03 Mar 2016 06:53:49 GMT

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

Re: Select from multiple tables

Former Member — Thu, 03 Mar 2016 10:01:59 GMT

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.