Select id from join and then use to select again
« Back
Tags:
Question ID: 106321
▲
1
▼
♥ 0

I would like to select, not just the ‘CYCL_FOLD.CF_FATHER_ID’ id, but its name.

SELECT
RUN.RN_RUN_ID,
RUN.RN_EXECUTION_DATE,
RUN.RN_EXECUTION_TIME,
RUN.RN_PC_VUSERS_INVOLVED,
RUN.RN_DURATION,
–RUN.RN_CYCLE_ID,
CYCLE.CY_FOLDER_ID,
CYCLE.CY_CYCLE,
CYCL_FOLD.CF_ITEM_NAME, –the folders name
CYCL_FOLD.CF_FATHER_ID –line above’s father folder id
–I ALSO WANT THE FATHER FOLDER’S NAME–
–CYCL_FOLD_TMP.CF_ITEM_NAME
FROM
RUN
INNER JOIN CYCLE ON RUN.RN_CYCLE_ID = CYCLE.CY_CYCLE_ID
INNER JOIN CYCL_FOLD ON CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
–INNER JOIN CYCL_FOLD_TMP ON CYCL_FOLD.CF_FATHER_ID = CYCL_FOLD_TMP.CF_ITEM_ID
ORDER BY
RUN.RN_RUN_ID ASC

I am having a real tough time explaining this in words. I want to use the third inner join to get the following:

SELECT CF_ITEM_NAME
FROM CYCL_FOLD
WHERE CF_ITEM_NAME=CF_FATHER_ID;

Any help would be greatly appreciated.

Marked as spam
Posted by (Questions: 6, Answers: 6)
Asked on August 10, 2015 8:10 pm
128 views
Answers (3)
▲
0
▼
✔
Private answer

http://screencast.com/t/yUcDkx3ku3HL

SELECT
r.RN_RUN_ID,
r.RN_EXECUTION_DATE,
r.RN_EXECUTION_TIME,
--r.RN_PC_VUSERS_INVOLVED,
--r.RN_DURATION,
--r.RN_CYCLE_ID,
c.CY_FOLDER_ID,
c.CY_CYCLE,
cf.CF_ITEM_NAME, --the folders name
cf.CF_FATHER_ID, --line above's father folder id
(select CF_ITEM_NAME from td.CYCL_FOLD where CF_ITEM_ID=cf.CF_FATHER_ID ) AS fathername
FROM
td.RUN AS r
INNER JOIN td.CYCLE AS c ON r.RN_CYCLE_ID = c.CY_CYCLE_ID
INNER JOIN td.CYCL_FOLD AS cf ON c.CY_FOLDER_ID = cf.CF_ITEM_ID
ORDER BY
r.RN_RUN_ID ASC

Marked as spam
Posted by (Questions: 6, Answers: 167)
Answered on August 11, 2015 4:02 pm
▲
0
▼
Thank you so much for the help!! I am getting the following error: ![alt text][1] [1]: /storage/temp/322-error.png
( at August 11, 2015 4:48 pm)
▲
0
▼
You probably just need a semi-colon after the end of the SQL statement. I was doing this in SQLServer. I don't have an Oracle server up to test on right now.
( at August 11, 2015 5:58 pm)
▲
0
▼
In the subquery line in the SELECT clause I don't think oracle wants the ''AS'' change: (select CF_ITEM_NAME from td.CYCL_FOLD where CF_ITEM_ID=cf.CF_FATHER_ID ) AS fathername to (select CF_ITEM_NAME from td.CYCL_FOLD where CF_ITEM_ID=cf.CF_FATHER_ID ) fathername
( at August 11, 2015 6:16 pm)
▲
0
▼
✔
Private answer

Thanks @Captain Mercury for the help!!! I needed to remove the 'AS' keywords for it to work.

SELECT
r.RN_RUN_ID,
r.RN_EXECUTION_DATE,
r.RN_EXECUTION_TIME,
r.RN_PC_VUSERS_INVOLVED,
r.RN_DURATION,
r.RN_CYCLE_ID,
c.CY_FOLDER_ID,
c.CY_CYCLE,
cf.CF_ITEM_NAME, --the folders name
cf.CF_FATHER_ID, --line above's father folder id
(select CF_ITEM_NAME from default_loadtesting_db.CYCL_FOLD where CF_ITEM_ID=cf.CF_FATHER_ID) as fathername
FROM
default_loadtesting_db.RUN r
INNER JOIN default_loadtesting_db.CYCLE c ON r.RN_CYCLE_ID = c.CY_CYCLE_ID
INNER JOIN default_loadtesting_db.CYCL_FOLD cf ON c.CY_FOLDER_ID = cf.CF_ITEM_ID
ORDER BY
r.RN_RUN_ID ASC;

Marked as spam
Posted by (Questions: 6, Answers: 6)
Answered on August 11, 2015 6:27 pm
▲
0
▼
✔
Private answer

Here is the Query for oracle.

SELECT
r.RN_RUN_ID,
r.RN_EXECUTION_DATE,
r.RN_EXECUTION_TIME,
--r.RN_PC_VUSERS_INVOLVED,
--r.RN_DURATION,
--r.RN_CYCLE_ID,
c.CY_FOLDER_ID,
c.CY_CYCLE,
cf.CF_ITEM_NAME, --the folders name
cf.CF_FATHER_ID, --line above's father folder id
(select CF_ITEM_NAME from CYCL_FOLD where CF_ITEM_ID=cf.CF_FATHER_ID ) fathername
FROM
RUN r
INNER JOIN CYCLE c ON r.RN_CYCLE_ID = c.CY_CYCLE_ID
INNER JOIN CYCL_FOLD cf ON c.CY_FOLDER_ID = cf.CF_ITEM_ID
ORDER BY
r.RN_RUN_ID ASC

Marked as spam
Posted by (Questions: 6, Answers: 167)
Answered on August 11, 2015 6:57 pm
« Back
EyeOnTesting

Welcome back to "EyeOnTesting" brought to you by Orasi Software, Inc.

Got it!
X
Scroll to Top