union subquery is not working in where condition (1 Viewer)

[mayyola]

error 3787​

access two columns combine to one column

Paperno | Expaperno
-----------------------
a1 | a2
a3 | a4

become

Paperno
--------
a1
a2
a3
a4


SELECT *
FROM jounals
WHERE Paperno not in (select Paperno from payment where paid='1' and len(Paperno)>1 UNION ALL select ExPaperno from payment where paid='1' and len(ExPaperno)>1 ) and revw='Accepted'

it's working when it's independent sentence: select Paperno from payment where paid='1' and len(Paperno)>1 UNION ALL select ExPaperno from payment where paid='1' and len(ExPaperno)>1

but when it's placed to where condition as subquery , it's invalid. i don't know what's going on.

Any help is appreciated

 

[sonic8]

It does make little sense, but it appears the database engine has difficulties using the UNION subquery directly in the where condition.

It works, if you wrap another subquery around it:

...
WHERE Paperno not in (SELECT PaperNo
                    FROM (select Paperno from payment where paid='1' and len(Paperno)>1
                          UNION ALL
                          select ExPaperno from payment where paid='1' and len(ExPaperno)>1)
                      AS dummy )
and revw='Accepted'

 

[arnelgp]

see this demo.
on query1 you create a Union (without any Filter/Criteria).
then create query2 against query1 where you put the criteria.
query2 is your final query.

 

[mayyola]

Hello,sonic8:

Thank you for reply and it works.but if writing in the website code to query,i'm not sure it's suitable or not. because it's like a loop. @@

it seems not best way to describe..

Thank you

 

[sonic8]

Thank you for reply and it works.but if writing in the website code to query,i'm not sure it's suitable or not. because it's like a loop

I don't understand your comment.
The subqueries discussed here are not like a loop. Neither in your original statement nor in my revised version.

 

[mayyola]

maybe i express not very well,i just want to know is there better codes to achieve the purpose.

Thank you

 

[sonic8]

i just want to know is there better codes to achieve the purpose.

I don't know of another, generally better, approach to query the same result.
In general SQL there are other options, e.g.:

SELECT j.*
FROM jounals j
LEFT JOIN (SELECT * FROM payment WHERE paid='1') AS p1
    ON j.Paperno = p1.Paperno
LEFT JOIN (SELECT * FROM payment WHERE paid='1') AS p2
    ON j.Paperno = p2.ExPaperno
AND j.revw='Accepted'
AND  (p1.Paperno IS NOT NULL
    OR p2.ExPaperno IS NOT NULL)

Whether this or any other possible approach is better, very much depends on your definition of "better". If "better" is primarily about performance, it can probably only be evaluated in your environment.

 

[Pat Hartman]

Access does not optimize subqueries well so Left Joins would be better. However, when your right-side table has criteria and you need to use a left join, then, create a separate querydef to apply the criteria to the right-side table. Then left join to the query.

 

[ebs17]

Any help is appreciated

Is it so?
In about 70 percent of the cases, a UNION is necessary because the table structures are wrong.
The structure of the table should look roughly in accordance with normal forms:

Paperno   Tag
a1          1
a2          2
a3          1
a4          2

The query then immediately becomes simpler and faster, a UNION prevents subsequent index use.