0

I'm trying to write something like this.

SELECT x,
,y
,(Select z from innerTable where innerTable.this = outterTable.that
FROM outterTable
where blahblah

Basically, this particular column I'd like to map to a value in another table. But when I try this, SQLDeveloper just prints out my Select statement as a column heading with nulls below it.

Steve
  • 4,457
  • 12
  • 48
  • 89
  • 1
    Why not use a join? – JohnHC Feb 23 '17 at 15:11
  • Can you please post some sample data and needed result? The naming of your tables is quite confusing: why do you query the "outer" table if it is "outer"? – Aleksej Feb 23 '17 at 15:24
  • I didn't want to use a join because this mapping is only applicable to the one column. It seemed cleaner to do it as a subquery. – Steve Feb 23 '17 at 15:36

2 Answers2

0

You can give the column an alias to fix the heading:

select x, y,
       (Select z from innerTable where innerTable.this = outterTable.that
       ) as z
from outterTable
where blahblah;

However, the NULL values are because there are no matches, so that is a different issue.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 2
    How would this behave if more than one value is in the z table? – JohnHC Feb 23 '17 at 15:13
  • 1
    @JohnHC . . . The OP states that the query runs. In that case, it would return an error, but that doesn't appear to be an issue. – Gordon Linoff Feb 23 '17 at 15:29
  • Thanks for the alias comment. When I do this select subquery on its own though (with a hard value in place of the outter table value) it works. And the values do seem to match in each table. So that's why I was thinking it must be an issue with my subquery syntax – Steve Feb 23 '17 at 15:43
0

SQL - Relationship between a SubQuery and an Outer Table

I think this covers it pretty clearly. What I'm trying to do would run very slowly, since it would calculate for each line in the resultset, so it is best that I find another way to do it.

Community
  • 1
  • 1
Steve
  • 4,457
  • 12
  • 48
  • 89