CODEWITHSUNDEEP
sqloracle-database
AndyPando
AndyPando

Reputation: 11

Complex Oracle query

I am trying to combine two queries into one and can't get my brain around how to nest the selects. The first query is like:

select SYSTEM,UPSTREAM_INTERFACE_ID,DOWNSTREAM_INTERFACE_ID 
from  SYSTEM_POLL where DEVICE_ID = '738224';

It returns the system name and up/down interfaces for a given device_id... pretty basic.

Where it gets complicated is I then need to look up the interface names for both the up/down interface from a separate table.

select * from INTERFACE_POLL where interface_id in 
(UPSTREAM_INTERFACE_ID,DOWNSTREAM_INTERFACE_ID) and SYSTEM_NAME = SYSTEM

Will return:

INTERFACE_ID * SYSTEM_NAME * POLL_DATE * INTERFACE_NAME
****************************************************************

84357177     * hillsboro.nj * 20151228 * cable-upstream  4/2/6.0

151617592    * hillsboro.nj * 20151228 * cable-downstream  8/7/6

How do you combine so that given a device ID the query will return the two associated INTERFACE_NAME values.

Thanks in advance for the help.

Upvotes: 1

Views: 85

Answers (3)

AndyPando
AndyPando

Reputation: 11

Thanks all, that was exactly what was needed! Since there are duplicate interface_id with different system names all I needed to add was a WHERE, but the following ultimately got me the result I needed:

SELECT sp.SYSTEM , i1.interface_name upstream_name , i2.interface_name downstream_name FROM SYSTEM_POLL sp INNER JOIN interface_poll i1 ON sp.upstream_interface_id = i1.interface_id INNER JOIN interface_poll i2 ON sp.downstream_interface_id = i2.interface_id WHERE DEVICE_ID = '738224' AND sp.SYSTEM = i1.SYSTEM_NAME AND sp.SYSTEM=i2.SYSTEM_NAME;

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35563

To join to a single table more than once, use table aliases

SELECT
      sp.SYSTEM
    , sp.UPSTREAM_INTERFACE_ID
    , sp.DOWNSTREAM_INTERFACE_ID
    , i1.interface_name upstream_name
    , i2.interface_name downstream_name
FROM SYSTEM_POLL sp
      INNER JOIN interface_poll i1 ON sp.upstream_interface_id = i1.interface_id
      INNER JOIN interface_poll i2 ON sp.downstream_interface_id = i2.interface_id
;

Upvotes: 4

OldProgrammer
OldProgrammer

Reputation: 12159

Use a table alias. Here is a simplified example:

select SYSTEM,UPSTREAM_INTERFACE_ID,DOWNSTREAM_INTERFACE_ID, i1.interface_name, i2.interface_name
from  SYSTEM_POLL, interface_poll i1, interface_poll i2
where system_poll.upstream_interface_id = i1.interface_id
and   system_poll.downstream_interface_id = i2.interface_id

Upvotes: 0

Related Questions