csehz
asked on
SQL - Subquery in WHERE section
Dear Experts,
Could you please have a short look on this code, actually in a training example would like to use a subquery in WHERE section but in this format it does not work:
Could you please advise how to change the syntax to be working but still staying the subquery in WHERE section?
The error message is
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Thanks in advance,
Could you please have a short look on this code, actually in a training example would like to use a subquery in WHERE section but in this format it does not work:
SELECT
a.LIFNR,
a.NAME1
FROM
AP.LFA1 a
WHERE
a.LIFNR = (SELECT b.LIFNR FROM AP.LFB1 b WHERE b.BUKRS = 'DE1')
Could you please advise how to change the syntax to be working but still staying the subquery in WHERE section?
The error message is
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Thanks in advance,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Caveat: My answer about the INNER JOIN wasn't complete, thus it's not correct.
The INNER JOIN can lead to duplicates in the output list. The INNER JOIN is in all cases
In this case the performance and execution plans my differ in comparison to the IN solution.
The INNER JOIN can lead to duplicates in the output list. The INNER JOIN is in all cases
SELECT a.LIFNR ,
a.NAME1
FROM AP.LFA1 a
INNER JOIN AP.LFB1 b ON a.LIFNR = b.LIFNR
WHERE b.BUKRS = 'DE1'
GROUP BY a.LIFNR ,
a.NAME1;
In this case the performance and execution plans my differ in comparison to the IN solution.
ASKER