0

I have 2 tables. TableA is the sales and TableB is the leads table. I'm trying to see if the people in the leads TableB made a purchase and their name appears on the sales TableA. If they do I only want the name of people who appears on both tables.

TableA (Sales)

 customer   school            email     address    state    phone   Sales
 john doe   hight middle    111@hu.z   111 updown   wi     2222222  $500 
 john doe   hight middle    111@hu.z   111 updown   wi     2222222  $100 
 zow hi     hight middle    222@hop.c  111 updown   wi     2222222  $200 
 calispo    dwight elem     s@dw.co.za 34 jimm elis CA     35450456 $1,000 
 Jordan     clift high       jp@hy.zut  1 unversity GA     11111111 $100 

TableB (Leads)

 customer       school           email       address    state
 john doe      hight middle    111@hu.z    111 updown     wi
 Kawi          hight middle     k@nop.c     111 updown    wi
 calispo       dwight elem    s@dw.co.za   34 jimm elis   CA

What I want to see:

 customer   school           email       address    state
 john doe   hight middle    111@hu.z   111 updown   wi     2222222   $500
 alispo     dwight elem     s@dw.co.za 34 jimm elis CA     35450456 $1,000

I try this query but it is returning duplicate of the info that I want

 SELECT a.email as [sales_school]
  ,b.email[leadds_insti]
  ,a.*
  ,b.*
  FROM [dbo].[Sales] a 
  right JOIN leads b ON a.email= b.email
2
  • 2
    Why do you want john doe with $500, not $100 (or sum $600) - is there any requirement which record to select? Jun 14, 2019 at 14:38
  • 1
    And general comment: don't use aliases a, b, c... for your tables, use something meaningful. Jun 14, 2019 at 14:39

2 Answers 2

1

You can try to find highest sale for each email and then join it with leads:

WITH maxsales AS
(SELECT 
sales.*,
ROW_NUMBER() (PARTITION BY sales.email ORDER BY Sales.Sales DESC) AS rn
FROM [dbo].[Sales]
) 
SELECT maxsales.email as [sales_school]
  ,leads.email[leadds_insti]
  ,maxsales .*
  ,leads.*
FROM maxsales 
INNER JOIN leads ON leads.email= maxsales.email
WHERE maxsales.rn = 1
1

If you only want the name of leads in the Sales table then this should do

SELECT TableB.*
FROM TableB
WHERE TableB.email IN
(SELECT email from TableA)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.