Click here to Skip to main content
15,078,444 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
table1(t1)
HTML
id name
1---a
2---b
3---c
4---d

table2(t2)
HTML
id t1_id date       count
1---1------1/1/2011--1
2---2------1/1/2011--2
3---1------2/1/2011--1
4---3------2/1/2011--3
5---4------3/1/2011--1

result
HTML
date     t1_name  count
1/1/2011--a--------1
1/1/2011--b--------2
1/1/2011--c--------0
1/1/2011--d--------0
2/1/2011--a--------1
2/1/2011--b--------0
2/1/2011--c--------3
2/1/2011--d--------0
3/1/2011--a--------0
3/1/2011--b--------0
3/1/2011--c--------0
3/1/2011--d--------1
Posted
Updated 15-Oct-13 2:23am
v4
Comments
Ankur\m/ 15-Oct-13 7:45am
   
That's one of the most basic join query. You should spend some time practicing them. You may not get help every time.
Zoltán Zörgő 15-Oct-13 8:08am
   
Sorry but this result is gibberish. You can't get this result with any kind of join. I can imagine what you want, but the expected result you have posted is nonsense: how could you compute 2/1/2011--c--------3 and 2/1/2011--c--------0 in the same result set?
Ankur\m/ 15-Oct-13 8:28am
   
Mike seems to have corrected it.
tushar Vayangankar 15-Oct-13 8:46am
   
2/1/2011--c--------3 and 3/1/2011--c--------0
Zoltán Zörgő 15-Oct-13 8:47am
   
Now, that's something else. I thought the same :)

SQL
with ctedatename (date,name,id)
As
(select distinct t2.date,t1.name,t1.id from table1 as t1 cross join table2 as t2 )
select t3.date, t3.name as t1_name, ISNULL(t4.count,0) as Count from ctedatename as t3 left outer join table2 as t4 on t3.id=t4.t1_id and t3.date=t4.date




Test results
Quote:
date t1_name Count
2011-01-01 a 1
2011-01-01 b 2
2011-01-01 c 0
2011-01-01 d 0
2011-02-01 a 1
2011-02-01 b 0
2011-02-01 c 3
2011-02-01 d 0
2011-03-01 a 0
2011-03-01 b 0
2011-03-01 c 0
2011-03-01 d 1



SQL statements used to load test data
SQL
CREATE TABLE [dbo].[Table1](
	[id] [int] NOT NULL,
	[name] [nvarchar](30) NOT NULL
) ON [PRIMARY]
Go
CREATE TABLE [dbo].[Table2](
	[id] [int] NOT NULL,
	[t1_id] [int] NOT NULL,
	[date] [date] NOT NULL,
	[count] [int] NOT NULL
) ON [PRIMARY]
Go
insert into table1 (id,name) values(1,'a')
insert into table1 (id,name) values(2,'b')
insert into table1 (id,name) values(3,'c')
insert into table1 (id,name) values(4,'d')
insert into table2 (id,t1_id,date,count) values (1,1,'2011-01-01', 1)
insert into table2 (id,t1_id,date,count) values (2,2,'2011-01-01', 2)
insert into table2 (id,t1_id,date,count) values (3,1,'2011-02-01', 1)
insert into table2 (id,t1_id,date,count) values (4,3,'2011-02-01', 3)
insert into table2 (id,t1_id,date,count) values (5,4,'2011-03-01', 1)
   
v7
Comments
tushar Vayangankar 15-Oct-13 7:40am
   
not working
Mike Meinz 16-Oct-13 13:53pm
   
My Solution 1 should work for you now.
SQL
   SELECT Result.date, T1.name, COALESCE( T2.[count], 0 ) AS count
     FROM (SELECT DISTINCT table2.date, table1.id
                      FROM table2
                CROSS JOIN table1) AS Result
     JOIN table1 AS T1 ON T1.id = Result.id
LEFT JOIN table2 AS T2 ON T2.t1_id = Result.id AND T2.date = Result.date


The following SELECT statement is use to get the data you want to see in the result set.
It uses a CROSS JOIN[^] to get all the possible combination with the unique dates from table2 and the id's from table1.
SQL
SELECT DISTINCT table2.date, table1.id
          FROM table2
    CROSS JOIN table1

tabel2.date	table1.id
2011-01-01	1
2011-01-01	2
2011-01-01	3
2011-01-01	4
2011-01-02	1
2011-01-02	2
2011-01-02	3
2011-01-02	4
2011-01-03	1
2011-01-03	2
2011-01-03	3
2011-01-03	4

Then this is joined[^] with table1 to get the name and table2 to get the count.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900