Left Join query not working

Question

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?

Pரதீப் · Accepted Answer · 2015-01-30 10:00:42Z

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)

This didn't work. I still have the record with ID 34 in my result set

souslov · Accepted Answer · 2015-01-30 10:04:36Z

1

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

edited Jan 30, 2015 at 10:04

answered Jan 30, 2015 at 9:52

souslov

44.7k11 gold badges92 silver badges113 bronze badges

3 Comments

Pரதீப் Over a year ago

It is better not to post a answer which is already present. Keep the SO clean. Existsto Not exists is not a big change here

souslov Over a year ago

@ NoDisplayName : I am not a big fan of such behavior either and I would probably delete my answer in any other situation, but as you can see, I was putting together a fiddle while you were posting an answer, so it took a while to edit my question, no offense.

Pரதீப் Over a year ago

@notulysses - I stand corrected. Mine is actually wrong

Pindub_Amateur · Accepted Answer · 2015-02-02 13:51:55Z

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);

Ankit Bajpai · Accepted Answer · 2015-02-02 14:06:03Z

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

Collectives™ on Stack Overflow

Left Join query not working

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Related