• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 578
  • Last Modified:

ORACLE -- JOIN "select" ?

How can I change the below
so it also extracts "ai.ID" ?

select si.ID, si.INFOTYPE, si.INFODATA1, si.INFODATA2 --, ai.ID
from GML_SUPPINFO si
where si.ID IN
  (
   SELECT ai.INFO_ID
   FROM GML_ACCTINFO ai,
        GML_ACCTMSTR am
   WHERE ai.AM_ID = am.ID
        AND am.id = :vVendorID
  )
----------------------------------------------
Oracle 10g tables attached.

Notes:
  GML_ACCTMSTR.ID links to GML_ACCTINFO.AM_ID
  GML_ACCTINFO.INFO_ID links to GML_SUPPINFO.ID
tables.txt
0
finance_teacher
Asked:
finance_teacher
1 Solution
 
tigin44Commented:
TRY
select si.ID, si.INFOTYPE, si.INFODATA1, si.INFODATA2 , AA.ID
from GML_SUPPINFO si
  INNER JOIN (
			   SELECT ai.INFO_ID, ai.ID
			   FROM GML_ACCTINFO ai,
					GML_ACCTMSTR am
			   WHERE ai.AM_ID = am.ID
					AND am.id = :vVendorID
			  ) AA ON si.ID  = AA.INFO_ID

Open in new window

0
 
finance_teacherAuthor Commented:
Thanks, below works great !

select si.ID, si.INFOTYPE, si.INFODATA1, si.INFODATA2 , AA.ID
from GML_SUPPINFO si
INNER JOIN (
             SELECT ai.INFO_ID, ai.ID
             FROM GML_ACCTINFO ai,
                  GML_ACCTMSTR am
             WHERE ai.AM_ID = am.ID
                   AND am.id = :vVendorID
           ) AA
           ON si.ID  = AA.INFO_ID
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now