CODEWITHSUNDEEP
mysqlsqlexists
nangys
nangys

Reputation: 115

WHERE EXISTS not working

I know I can do this with a table join, however, I'm trying to do it with a WHERE EXISTS clause. I know there are 65 matches, nonetheless, the sub query is not working. It brings all the values from table scrubs.

pdwspend.Child Supplier ID has many duplicates values that why I use DISTINCT in the sub query.

SELECT DISTINCT sc.`BW Parent Number`
FROM scrubs sc
WHERE EXISTS (

    SELECT DISTINCT pdwspend.`Child Supplier ID`
    FROM pdwspend
    WHERE pdwspend.`BU ID` = 'BU_1'
    AND pdwspend.version LIKE '%GOV%'
    )
AND SC.`year` =2014

Thanks

Upvotes: 0

Views: 124

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I suspect you want a correlated subquery. Right now, the exists either finds a row (and everything is returned) or finds no row (and nothing is returned). Here is a guess as what the query you want looks like:

SELECT DISTINCT sc.`BW Parent Number`
FROM scrubs sc
WHERE EXISTS (SELECT 1
              FROM pdwspend s
              WHERE s.`BU ID` = 'BU_1' AND s.version LIKE '%GOV%' AND
                    s.`Child Supplier ID` = sc.`BW Parent Number` 
             ) AND
      SC.`year` = 2014;

Also note that distinct is not needed for exists subqueries.

Upvotes: 2

Related Questions