CODEWITHSUNDEEP
sqlsubqueryfirebirdfirebird2.5
malyGatsby
malyGatsby

Reputation: 11

"SELECT column ... WHERE", knowing value of other column, can't use subquery

I'm working on this SELECT query:

SELECT * FROM x 
JOIN y ON x.struktorg = y.ref
WHERE y.rodzic = (SELECT ref FROM y WHERE y.symbol = 'the value i know')

The goal is to not use subselect. I know the value of symbol column, but the table that I need to get results from doesn't use it. It uses the reference number of that value.

Upvotes: 1

Views: 357

Answers (3)

eshirvana
eshirvana

Reputation: 24603

you can join to y one more time:

SELECT * FROM x 
JOIN y y1 ON x.struktorg = y1.ref
join y y2 
  ON y1.rodzic = y2.ref
  and y2.symbol = 'the value i know'

but I don't see any benefit using join over subquery in this scenario .

Upvotes: 2

joserobertog
joserobertog

Reputation: 119

if the subquery table y is the same of the JOIN y, then you can do this

SELECT * 
FROM x 
JOIN y ON x.struktorg = y.ref and y.rodzic = y.ref and y.symbol = 'the value i know'

if the subquery table y is diferent of the JOIN y, then you can do this renaming subquery table y for z

SELECT * FROM x 
JOIN y ON x.struktorg = y.ref
JOIN z ON y.rodzic = z.ref and z.symbol = 'the value i know'

Upvotes: 1

Michele La Ferla
Michele La Ferla

Reputation: 6884

I would go around the sub-select by creating a temporary table first, like in the example below:

SELECT ref INTO #TEMP_TABLE FROM y WHERE y.symbol = 'the value i know'

Then I would join on that temporary table I created like in the example here:

SELECT * FROM x 
JOIN y ON x.struktorg = y.ref
JOIN #TEMP_TABLE z on z.ref = y.rodzic

Having said that, I am sure that the above solution works effectively for SQL Server. However, I've never used Firebird, so the principles there might be different.

Upvotes: 0

Related Questions