sql join with two tables

Question

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 15-Oct-13 0:57am

tushar Vayangankar

Updated 15-Oct-13 2:23am

Mike Meinz

v4

Add a Solution

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 :)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mike Meinz · Answer 1 · 2013-10-15T01:26:00

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)

André Kraak · Answer 2 · 2013-10-15T02:09:00

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.

sql join with two tables

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0