CODEWITHSUNDEEP
sql
Cooliozar
Cooliozar

Reputation: 5

SQL - How do I filter for specific data instead of returning all results?

In this query I'm getting almost everything I need. The only problem is I want to eliminate the applications that DON'T meet BOTH criteria.

Criteria:

show app_id's where rel_data for field_id 22222 = '' AND rel_data for field_id 55555 = U.

SELECT distinct app_id, alr.loan_id, field_id, rel_data from loan_field_rel lfr
LEFT JOIN app_loan_rel alr ON alr.loan_id=lfr.loan_id
WHERE field_id IN (22222, 55555) AND rel_data IN ('', 'U') 
GROUP BY app_id, rel_data, alr.loan_id, field_id
ORDER BY app_id

I should see only lines like this with 2808 results, but I'm getting 13,000 results:

app_id          loan_id    field_id     rel_data
25791           791        22222    
25791           791        55555        U

I know it's something really simple but any help is appreciated.

Upvotes: 0

Views: 55

Answers (2)

onupdatecascade
onupdatecascade

Reputation: 3366

... where ( field_id = 22222 and rel_data = '' ) OR ( field_id = 55555 and rel_data = 'U' )

Correction:

SELECT distinct app_id, alr.loan_id, field_id, rel_data 
from app_loan_rel alr
LEFT JOIN loan_field_rel lfr22222 ON alr.loan_id=lfr.loan_id and field_id = 22222
LEFT JOIN loan_field_rel lfr55555 ON alr.loan_id=lfr.loan_id and field_id = 55555
WHERE lfr22222.rel_data = ''
    AND lfr55555.rel_data = 'U'
GROUP BY app_id, rel_data, alr.loan_id, field_id
ORDER BY app_id

Upvotes: 1

kostas
kostas

Reputation: 461

  where  (  field_id=2222 and rel_data='') or (field_id=5555 and rel_data=U)

Upvotes: 0

Related Questions