CODEWITHSUNDEEP
mysqlsqljoin
Pindub_Amateur
Pindub_Amateur

Reputation: 328

Left Join query not working

I have 2 tables.

Table 1:
ID     Name      Age       PhoneNumber 
12     Joe       25        873827382
23     Bob       28        928398233
34     Jane      23        237828883


Table 2:
ID     Agent       QuantitySold    
12      A1            100
23      B1            300
12      C1            600
34      A2            400
34      B1            800
23      B2            900

I want to show all the details of the employees who have never sold a quantity not equal to 800.

SELECT a.ID, a.Name, a.Age, a.PhoneNumber
FROM table1 a LEFT JOIN table2 b
ON a.ID= b.ID AND b.quantity <> 800

I want a result set that doesn't have ID 34 in it. But I cant seem to achieve that. Any help?

Upvotes: 0

Views: 55

Answers (4)

Ankit Bajpai
Ankit Bajpai

Reputation: 13527

Why you are using Left Join. Use inner join rather. Something like this:-

SELECT a.ID, a.Name, a.Age, a.PhoneNumber, SUM(b.quantity)
FROM table1 a JOIN table2 b
ON a.ID= b.ID 
GROUP BY b.Agent
HAVING SUM(b.quantity) <> 800

Upvotes: 0

Pindub_Amateur
Pindub_Amateur

Reputation: 328

This is what finally worked. 

SELECT a.ID
     , a.Name
     , a.Age
     , a.PhoneNumber
FROM table1 a
WHERE a.ID NOT IN (SELECT ID FROM table2 QuantitySold = 800);

Upvotes: 0

potashin
potashin

Reputation: 44601

You can use NOT EXISTS without any of the JOINs:

SELECT a.ID
     , a.Name
     , a.Age
     , a.PhoneNumber
FROM table1 a
WHERE NOT EXISTS (SELECT * FROM table2 WHERE ID = a.ID AND QuantitySold = 800)

By the way, the column name is QuantitySold, not quantity.

JSFiddle

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

You need to change your Left Join to Inner Join.

Left Join will bring all the rows from table1 even though he never made a sales. I will do this using Exists.

SELECT a.ID,
       a.Name,
       a.Age,
       a.PhoneNumber
FROM   table1 a
WHERE  EXISTS (SELECT 1
               FROM   table2 b
               WHERE  a.ID = b.ID
                      AND b.quantity <> 800)

Upvotes: 1

Related Questions