CODEWITHSUNDEEP
mysqljoin
BasicGem
BasicGem

Reputation: 83

Not able to get correct records in query - php mysql

I am running the following query. 

SELECT * 
  FROM exam, result 
 WHERE exam.home = result.external_id 
   AND exam.away != result.external_id;

Records in the exam table:

home   away
51156   8
51158   7
51158  51182

Records in result table:

external_id
51156
51158
51182

In normal scenario, the first two records should be fetched. Now when I use the '!=' independently, it executes without an issue. Means, when I execute 

SELECT * FROM exam, result WHERE exam.away != result.external_id

it is executed correctly. This is the result. 

home   away
51156   8
51158   7

However, in my above mentioned erroneous query, the output is as follows:

id  home   away
111 51156   8
100 51158   7
123 51158  51182

The last row it is considered though it should not be counted. Can anyone tell me why? Or any alternative for the query?

Upvotes: 1

Views: 67

Answers (3)

Punit
Punit

Reputation: 1120

SELECT *  
  FROM exam  
 WHERE home IN (SELECT external_id FROM result WHERE exam.home = result.external_id)  
   AND away IN (SELECT external_id FROM result WHERE exam.away!= result.external_id )

Upvotes: 0

Shef
Shef

Reputation: 45599

To my understanding you are looking for records which do not have an id on the column away that is present in the table result.

If that is the case, then this query will produce the result you want:

SELECT *
  FROM exam
 WHERE home IN (SELECT external_id FROM result)
   AND away NOT IN (SELECT external_id FROM result)

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

try this:

SELECT e.home,e.away,r.external_id
FROM exam e join result r on r.external_id = e.home and e.away != r.external_id 
where e.away not in (select r.external_id from result r)

Upvotes: 0

Related Questions