Left join to a table which may or not contain the data

Question

I have the following tables : client and shop. They have the following columns :

Client : Id, Name, Surname, Email

Shop : Id, Name, ClientId

ClientId is a foreign key to table Client.

I need to do a search based on the name AND surname OR Email and if there is any entry in the Shop table then return it but should not return the Id passed as parameter.

I have tried using a left join as follows but this does not work:

select *
from Client c 
left join Shop s
on c.Id  = s.ClientId
where c.Name = 'abc'
and c.Surname = 'xyx'
and c.Id != Id
or c.Email = 'ab@cde.com'
and c.Id != Id

But this is not returning me the correct data.

For example, I want to seach the name Jon and surname Sam with email jon@samy.com and clientId = 201.
So the request should return me all client having the name Jon and surname Sam or having email jon@samy.com and if there is any entry with these details in the table Shop return it but excluding client with Id 201.

I would also like to convert this SQL to LinQ.

Any idea what is wrong with my request?

Why does `Shop` have a `ClientId`? That means that one client has 0..n shops and a shop only 1 client. I'd expect the relationship to be many to many, i.e. with a junction table. — Gert Arnold, Sep 16 '18 at 15:44
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Sep 17 '18 at 16:42

Zaynul Abadin Tuhin · Answer 1 · 2018-09-16T15:36:44.017

0

use inner join instead left join and use or condition and add union as you want data from client if no data in shop

    select c.*
    from Client c 
    inner join Shop s
    on c.Id  = s.ClientId
    where c.Name = 'abc'
    or c.Surname = 'xyx'
    or c.Email = 'ab@cde.com'
   union
   select * from Client c
   where c.Name = 'abc'
    or c.Surname = 'xyx'
    or c.Email = 'ab@cde.com'

edited Sep 16 '18 at 15:36

answered Sep 16 '18 at 15:12

Zaynul Abadin Tuhin

31,407
5
33
63

If I use inner join the request will not return any data if there is no record in the Shop table – refresh Sep 16 '18 at 15:18
@tabby so do you want return data in case no data in shop? – Zaynul Abadin Tuhin Sep 16 '18 at 15:23
Yes in case there is data in table Shop, it should return it. Else return only those found in the Client table. – refresh Sep 16 '18 at 15:33
Do you have any idea why left join is not working here? – refresh Sep 16 '18 at 15:56

Tony Thomas · Answer 2 · 2018-09-16T17:15:23.673

Seems like you need to do an EXISTS rather than a LEFT JOIN as there could be a client appearing in different shops:

select *
from Client c
where exists(
    select * from Shop s
    where c.Id = s.clientid
)
    and c.Name = 'Jon'
    and c.Surname = 'Sam'
    and c.Id <> 201

If you insist on LOJ:

select *
from Client c
left outer join Shop s
    on c.Id = s.ClientId
where s.Id is not null
    and c.Name = 'Jon'
    and c.Surname = 'Sam'
    and c.Id <> 201

LINQ:

var searchResult = from c in clients
               where shops.Any(s => s.ClientId == c.Id)
                   && c.Name.Equals("Jon")
                   && c.Surname.Equals("Sam")
                   && c.Id != 201
               select c;

score 0 · Accepted Answer · answered Sep 17 '18 at 08:43

I am not sure if I understood the requirement completely. I am writing 2 queries. One of these should work in your case

If you want to exclude shop entries of the Id but want client entries

SELECT * FROM Client c 
LEFT JOIN (SELECT * FROM Shop WHERE ClientId != Id) s ON c.Id  = s.ClientId
WHERE (c.Name = 'abc' AND c.Surname = 'xyx') OR (c.Email = 'ab@cde.com')

If you want to exclude client entries

SELECT * FROM Client c 
LEFT JOIN Shop s ON c.Id  = s.ClientId
WHERE (c.Name = 'abc' AND c.Surname = 'xyx' AND c.Id != Id) OR (c.Email = 'ab@cde.com' AND c.Id != Id)

If it works, then let me know. I will help you with LINQ query. But try to write it yourself.

Left join to a table which may or not contain the data

3 Answers3