MYSQL Finding server with failed status

Question

I have sql table with following columns id as PK, ip, check_type check_status:

id      ip             check_type    check_status
1       10.10.10.10    check1        FAIL
2       10.10.10.10    check2        PASS
3       10.10.10.11    check1        PASS
4       10.10.10.11    check2        PASS

I want only the ips which have Failed Check1 and Passed Check2 from the table. So, from above example my output should be 10.10.10.10

SELECT DISTINCT
  (ip)
FROM table1 a
INNER JOIN table1 b
  ON a.ip = b.ip
WHERE a.check_status = 'FAIL'
AND a.check_type = 'check1'
AND b.check_status = 'PASS'
AND b.check_type = 'check2';

I am getting an error

column ip is ambiguous

Answer 1

SELECT DISTINCT
  (a.ip)
FROM table1 a
INNER JOIN table1 b
  ON a.ip = b.ip
WHERE a.check_status = 'FAIL'
AND a.check_type = 'check1'
AND b.check_status = 'PASS'
AND b.check_type = 'check2';

This will work

Your are selecting ip that is in both a and b so it is giving error so when you write a.ip or b.ip it will work fine