CODEWITHSUNDEEP
sql
mojo
mojo

Reputation: 59

SQL query on similarity

I have to display all the customers who have been referred by a referrer with the same last name as the customer.

Upvotes: 1

Views: 275

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You can use a self-join as

select c1.customer#, c1.lastname, c1.city, c1.zip, c1.referred 
  from customers c1 
  join customers c2 
    on c1.customer# = c2.referred
   and c1.lastname = c2.lastname;

customer#   lastname    city       zip     referred
---------   --------  -----------  ------  ---------
1003        SMITH     TALLAHASSEE   32306   NULL

Rextester Demo

Upvotes: 1

NikuNj Rathod
NikuNj Rathod

Reputation: 1658

You can try this query 

select cust.*, cust_ref.*
from customers cust,
referred cust_ref
where cust_ref.lastname = cust.lastname

Note : You can select field according your requirement.

I hope it will used.

Upvotes: 1

Related Questions