1

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
malyGatsby
  • 11
  • 1
  • Why don't you want to use the subquery? – Gordon Linoff Jun 04 '21 at 14:01
  • from what ive heard, firebird has some performance issue with subqueries. the only thing that i'm sure of is that my boss isn't keen on typing it like i did and i was asked to avoid the subselect, yet i have no idea how – malyGatsby Jun 04 '21 at 14:12

3 Answers3

2

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 .

eshirvana
  • 23,227
  • 3
  • 22
  • 38
1

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'
joserobertog
  • 109
  • 4
  • `and y.rodzic = y.ref` this is not what topicstarter wanted. You want one single row in Y with two columns having same value. He wanted two different rows from Y with Y being tree-like table, having its rows interlinked through columns. – Arioch 'The Jun 06 '21 at 09:11
0

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.

Michele La Ferla
  • 6,775
  • 11
  • 53
  • 79
  • 1
    Firebird only knows global temporary tables, which have to be explicitly created using the `create global temporary table` DDL statement, which requires sufficient permission to create tables, and are not intended for one-off usage. – Mark Rotteveel Jun 04 '21 at 16:15
  • Thanks for the info @Mark. Sorry I wasn't aware of this – Michele La Ferla Jun 06 '21 at 05:04