0

I havetwo tables, Table A with Customer ID as well Sales from online purchase and Table B with Customer IDs as well as Sales from Store Purchase. My Output should consist of Customer ID from both tables with a column that says, "Store", "Online", "Both" depending on the kind of purchase the customer made.

Dale K
  • 25,246
  • 15
  • 42
  • 71
shanthechanchan
  • 1
  • 1
  • Please show your current code – MSpiller Mar 09 '20 at 20:48
  • Please provide sample data and desired results. – Gordon Linoff Mar 09 '20 at 20:49
  • MySQL or SQL Server? Which RDBMS are you using? – Dale K Mar 09 '20 at 20:50
  • 1
    Welcome to Stack Overflow. To help us help you, please take the [Tour](https://stackoverflow.com/tour) and read through [How To Ask](https://stackoverflow.com/help/how-to-ask). Take a look at this [well-structured question](https://stackoverflow.com/questions/60453346/use-or-conditions-in-where-clause-as-column-names-in-result), then [edit](https://stackoverflow.com/posts/60608125/edit) your question with the details needed to create [a Minimal, Complete, and Verifiable Example for database-related questions](https://dba.stackexchange.com/help/minimal-reproducible-example). – Eric Brandt Mar 09 '20 at 20:51
  • Please tag the actual RDBMS of interest. – Dale K Mar 09 '20 at 21:49
  • Please read this guide on how to ask question. https://stackoverflow.com/help/how-to-ask – Eric Mar 09 '20 at 21:54

2 Answers2

0

You can use union all and some case logic:

select customerid,
       (case when max(is_online) = 1 and max(is_instore) = 1 then 'both'
             when max(is_online) = 1 then 'online'
             else 'instore'
        end) as grouping
from ((select customerid, 1 as is_online, 0 as is_instore
       from a
      ) union all
      (select customerid, 0 as is_online, 1 as is_instore
       from b
      ) 
     ) ab
group by customerid;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0
select 
 c.customer_id, 
 case when count(*) = 2 then 'both' else c.location end as location
from (
       select a.customer_id, 'store' as location from ta a 
       union 
       select b.customer_id, 'online' as location from tb b
     ) c 
group by c.customer_id
Vasyl Pominchuk
  • 13
  • 4