CODEWITHSUNDEEP
sql
MulOnPomm
MulOnPomm

Reputation: 145

Left join brings every row from table B

I have done a very simple example with 2 tables:

Table A: user_client

id   | username
---------------
2222 | test

Table B: user_client_contact

client_id | country
--------------------
2222      | latvia
2222      | estonia

I get the result:

id
-----
2222
2222

but the result what I expect is:

id
-----
2222

SQL:

select 
    user_client.id
from user_client
left join user_client_contact on user_client_contact.client_id = user_client.id

See the sqlfiflle here: http://sqlfiddle.com/#!9/270cc/7

Upvotes: 1

Views: 544

Answers (6)

Eli
Eli

Reputation: 2608

The reason why 2 rows were returned is due to the fact that all rows in the user_client_contact table have the same value as the user_client_id in the user_client table; therefore the result has two rows. If you want to see only one row for each of the rows in the user_client table, you'll need to add a "distinct" clause into your query. Please see the query below:

select DISTINCT
    user_client.id
from user_client left join user_client_contact
    on user_client_contact.client_id = user_client.id

Upvotes: 3

Kritner
Kritner

Reputation: 13765

Just use the distinct keyword to eliminate the duplicate:

select distinct
    user_client.id
from user_client
left join user_client_contact on user_client_contact.client_id = user_client.id

if you only want user_client.ids that have an entry in the user_client_contact table, you should be using an inner join, not a left join:

select distinct
        user_client.id
    from user_client
    inner join user_client_contact on user_client_contact.client_id = user_client.id

The reason you're getting two rows, is because your 1 row in user_client is able to join to two rows within user_client_contact. If you were selecting everything from the query like with:

select *
from user_client
left join user_client_contact on user_client_contact.client_id = user_client.id

you would see that you're getting two distinct rows, one as 2222,2222,latvia and one as 2222,2222,estonia. Since you're only selecting one column of 3 from the columns between the two tables, they look to be duplicate. As I said at the beginning, using distinct would give you only the unique values.

Upvotes: 2

kemiller2002
kemiller2002

Reputation: 115538

Based on your query: 

select 
    user_client.id
from user_client
left join user_client_contact on 
user_client_contact.client_id = user_client.id

You are joining id and client_id. There are two entries in user_client_contact with the value of 2222, and so that means that the user_client.id will appear twice, one fore each matching row in user_client_contact.

You can solve this a couple of different ways the easiest is to do: 

select 
    distinct user_client.id
from user_client
left join user_client_contact on 
user_client_contact.client_id = user_client.id

You could also do: 

   SELECT user_client.id FROM user_client 
     WHERE User_client.id IN (select client_id FROM user_client_contact)

NOTE: There is a misconception that subqueries will automatically be slower, but this is not necessarily the truth. It does vary on a case by case basis, but normally the optimizer will account for subqueries and speed them up, especially compared to distinct. Here is an article comparing the two.

Upvotes: 3

Nicholas Carey
Nicholas Carey

Reputation: 74355

A left join brings in every row from the left table at least once (and repeated for every matching row in the right table)...which is why you're getting the duplicates.

What you probably want to do instead is use a correlated subquery in conjunction with exists:

select uc.id
from user_client uc
where exists ( select *
               from user_client_contact ucc
               where ucc.client_id = uc.id
             )

Upvotes: 4

Matthew McPeak
Matthew McPeak

Reputation: 17944

That's what a left join does. If you don't want information from table B, it sounds like maybe you want a semi-join. Like this:

select 
    user_client.id
from user_client
where exists ( SELECT 'contact' FROM user_client_contact WHERE user_client_contact.client_id = user_client.id )

Or, if you only want a single row from table B, you just need to specify more conditions in your WHERE clause.

Upvotes: 4

duffy356
duffy356

Reputation: 3716

You can add distinct to the select statement:

select distinct user_client.id
from user_client
left join user_client_contact 
  on user_client_contact.client_id = user_client.id

The DISTINCT keyword can be used to return only distinct (different) values.

More information about distinct.

Upvotes: 2

Related Questions