Link to home
Start Free TrialLog in
Avatar of csehz
csehzFlag for Hungary

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:

SELECT
	a.LIFNR,
	a.NAME1
FROM
	AP.LFA1 a
WHERE
	a.LIFNR = (SELECT b.LIFNR FROM AP.LFB1 b WHERE b.BUKRS = 'DE1')

Open in new window


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
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of csehz

ASKER

Thank you for the answers, I learnt from both of them, actually to my scenario the second solution fits better
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

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;

Open in new window


In this case the performance and execution plans my differ in comparison to the IN solution.