0

The problem is as following: I'm using a subquery and I need to join

WITH subselect_from_A AS (
    SELECT A.A_ID FROM A
)
SELECT B.D_ID FROM B, subselect_from_A WHERE B.B_ID = 
A.A_ID
/* or instead: subselect_from_A.A_ID */

;

How Do I reference the column form the subselect?

Chris
  • 1,119
  • 1
  • 8
  • 26

2 Answers2

2

First of all, you should stop using deprecated implicit joins and use explicit joins instead. Then you can use table aliases for this:

WITH subselect_from_A AS (
    SELECT A.A_ID FROM A --doesn't really seem like a sub-select
)
SELECT B.D_ID, A.A_ID
FROM B
INNER JOIN subselect_from_A AS A
    ON B.B_ID = A.A_ID;
Lamak
  • 69,480
  • 12
  • 108
  • 116
  • I agree that the explicit join increases readibility, but isn't it just the implicit `OUTER JOIN` that is deprecated? – wvdz Apr 16 '14 at 14:29
  • @popovitsj That it's deprecated doesn't meand that it doesn't work anymore, just that there are newer and bette ways to do it. The ANSI 92 SQL syntax for joins is the one posted in my answer, it's over 20 years old and the implicit joins were removed from the ANSI standard – Lamak Apr 16 '14 at 14:33
  • Accepted as answer - either Lamak and popovitsj were right, but I kinda like the explicit join – Chris Apr 16 '14 at 14:58
  • Yes - don't make it so hard on the reader and the optimizer. If you want the join write the JOIN :-) – Falco Apr 16 '14 at 15:25
1

Same way you would with a regular table.

WITH subselect_from_A AS (
    SELECT A.A_ID FROM A
)
SELECT B.D_ID, subselect_from_A.A_ID FROM B, subselect_from_A
WHERE B.B_ID = subselect_from_A.A_ID;
wvdz
  • 16,251
  • 4
  • 53
  • 90