CODEWITHSUNDEEP
sql
ann
ann

Reputation: 19

How to receive this result in SQL

I have two tables – customer and product and I have 10 customers and 5 products.

I can join this for example by query:

select 
    ca.id as customer_id,p.id as product_id
from 
    customeraccount ca  
left join 
    product p on ca.id = p.customeraccountid

How to recive new table which is similar to Cartesian but which isn’t Cartesian, like this:

enter image description here

Flaq is a variable which says if this customer have this product.

Upvotes: 0

Views: 80

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

If you have two tables you shoul re-think your design. You should at least have a table for your customers, one for your products and one for actual sales (or whatever you want to do here...).

As you are rather new here, please allow me one hint: Help the SO community with a good MCVE. Here I create one for you:

SQL-Server syntax...

DECLARE @customer TABLE(CustomerID INT);
INSERT INTO @customer VALUES(1001),(1002),(1003);
DECLARE @prod TABLE(ProdID INT);
INSERT INTO @prod VALUES(1),(2),(3);
DECLARE @sales TABLE(CustomerID INT,ProdID INT);
INSERT INTO @sales VALUES(1001,1),(1001,3)
                        ,(1002,1),(1002,2)
                        ,(1003,2);

SELECT c.CustomerID
      ,p.ProdID
      ,CASE WHEN s.CustomerID IS NULL THEN 0 ELSE 1 END AS HasSale 
FROM @customer AS c
CROSS JOIN @prod AS p
LEFT JOIN @sales AS s ON s.ProdID=p.ProdID AND s.CustomerID=c.CustomerID

The result

Cust   Prod HasSales
1001    1   1
1001    2   0
1001    3   1
1002    1   1
1002    2   1
1002    3   0
1003    1   0
1003    2   1
1003    3   0

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

CROSS JOIN may solve your problem

SELECT ca.id as customer_id,
       p.id as product_id,
       CASE WHEN ca.id=p.customeraccountid THEN 1 ELSE 0 END AS Flag
FROM  customeraccount ca  
CROSS JOIN product p

Upvotes: 1

Related Questions