CODEWITHSUNDEEP
sqlsql-server
karan saxena
karan saxena

Reputation: 41

JOIN TWO TABLES WITH KEY VALUES IN TABLE 1 WITH ALL KEY VALUES IN TABLE 2

I have two tables

Table 1: With Column Name Key Values: 

C1
C2
C3
C4

Table 2: with three columns Product, Category, count 

P1-C1-2  
P1-C3-4  
P2-C1-3  
P2-C2-4,  
P2-C3-8,   
P3-C3-10,  
P3-C4-2,

Output required: 

P1-C1-2  
P1-C2-0  
P1-C3-0  
P1-C4-0  
P2-C1-3  
P2-C2-4,  
P2-C3-8,  
P2-C4-0,  
P3-C1-0,  
P3-C2-0,  
P3-C3-10,  
P3-C4-2

Is there any way to do it?

Thanks in Advance

Upvotes: 0

Views: 63

Answers (3)

Moinul Islam
Moinul Islam

Reputation: 469

SELECT t2.Product,t2.Category, case when t2.Category=t1.Category then t2.count ELSE 0 END AS count
FROM table1 t1,table2 t2;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You are looking for cross join

select distinct t2.Product, t1.Key, coalesce(t3.count, 0) as count 
from table2 t2 cross join (select [Key] from table1) t1
left join table2 t3 
          on t3.Product = t2.Product and t1.[key] = t3.Category

Upvotes: 1

Max Larionov
Max Larionov

Reputation: 474

Looks like you want to use the cross-join:

SELECT [* | column_list]
FROM table1
CROSS JOIN table2;

More on the subject

Upvotes: 0

Related Questions