CODEWITHSUNDEEP
sqloracle-database
khansen
khansen

Reputation: 205

How can I JOIN a table with the result of a UNION in Oracle?

I have 3 tables in an Oracle database. FooDBflights, FooDB1 and FooDB2.

I use the "with" operator to create simpler version of these tables named, FLIGHTS, MESSAGES1 and MESSAGES2.

I want to make a select statement that returns one table. FLIGHTS joined with the union of MESSAGES1 and MESSAGES2

Here is my SQL statement.

WITH FLIGHTS AS (
    SELECT DISTINCT id,ARCADDR,CALLSIGN,trunc(FIRSTTIMEENTRY)
    AS DOE FROM FooDBflights
    WHERE FIRSTTIMEENTRY IS NOT null),
MESSAGES1 AS (
    SELECT DISTINCT flightID,AIRCRAFTADDRESS,trunc(SYS_DATETIME)
    FROM FooDB1
    WHERE AIRCRAFTADDRESS!=' ' ),
MESSAGES2 AS ( 
    SELECT DISTINCT flightID,AIRCRAFTADDRESS,trunc(SYS_DATETIME)
    FROM FooDB2
    WHERE AIRCRAFTADDRESS!=' ' )

SELECT a.*,b.*,substr(b.AIRCRAFTADDRESS, 3)
FROM FLIGHTS a
    LEFT JOIN MESSAGES1 b
    ON a.callsign=trim(b.flightid)
    AND trim(a.arcaddr)=substr(UPPER (b.AIRCRAFTADDRESS), 3)

This query returns FLIGHTS and MESSAGES1 joined perfectly, but I can't figure out how to make the union between MESSAGES1 and MESSAGES2. How can I do this?

Upvotes: 0

Views: 313

Answers (1)

trincot
trincot

Reputation: 350272

It would be the easiest to perform the union in the with clause:

Instead of

MESSAGES1 AS ...
MESSAGES2 AS ...

Do:

MESSAGES AS (
    SELECT flightID,AIRCRAFTADDRESS,trunc(SYS_DATETIME) AS DT
    FROM FooDB1
    WHERE AIRCRAFTADDRESS!=' '
    UNION 
    SELECT flightID,AIRCRAFTADDRESS,trunc(SYS_DATETIME)
    FROM FooDB2
    WHERE AIRCRAFTADDRESS!=' ' )

... and reference that one in our main SELECT query.

Note that DISTINCT is not needed once you do UNION.

Remark

Using trim() in your join conditions could make the query slow when there is a lot of data: the database engine cannot benefit from indexes. It would be better to have foreign keys, and enforce those as database constraints. Then trim will not be necessary.

Also substr() and upper() will potentially have a negative effect on performance. Consider splitting the field AIRCRAFTADDRESS into two in the table itself, and make sure the text is already in uppercase on insertion.

Upvotes: 1

Related Questions