• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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