Search code examples
sqldb2

DB2 SQL: How do i add a sub select statement to the where clause?

So I have a query as follows :

SELECT a.abc, b.xyz, c.yup
from coin  a, true  b, yes  c
where a.id = b.id
and b.id = c.id
and a.access_code in 
(select ax.acess_code from coin ax, Parameter b
where ax.flow_id = b.flow_id
and b.start_date = '2022-06-21'
and b.result = 'B')

However, I get this error: *SQL Error [42601]: An unexpected token "" was found following "". Expected tokens may include: "

WITH ur".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.21.29*

Any ideas?

I am trying to link the two queries together but have been unsuccessful. Note that the table 'coin' is the same table in the main query as in the sub query.

Solution

  • You should use different aliases and start using JOIN

    Also you can use also an INNER JOIN instead of an IN clause

    SELECT a.abc, b.xyz, c.yup
from coin  a
INNER JOIN  true  b ON a.id = b.id
INNER JOIN  yes  c ON b.id = c.id
INNER JOIN 
(select ax.acess_code 
from coin ax
INNEr JOIN  Parameter bx
ON  ax.flow_id = bx.flow_id
WHERE bx.start_date = '2022-06-21'
and bx.result = 'B') d ON a.access_code = d.acess_code