select from two table

Question

1.00/5 (1 vote)

See more:

Table t1

project---godown
0-----------11
0-----------12
1-----------11
1-----------12
2-----------11
2-----------12

Table t2

project---godown---product---qty
0-----------11-------22--------5
1-----------12-------23--------6
2-----------11-------24--------7

Result
project---godown---product---qty
0-----------11-------22--------5
0-----------12-------22--------0
1-----------11-------23--------0
1-----------12-------23--------6
2-----------11-------24--------7
2-----------12-------24--------0

Posted 17-Dec-13 0:17am

tushar Vayangankar

Add a Solution

Comments

joginder-banger 17-Dec-13 6:33am

for more information and Query hit to Reply

4 solutions

Solution 1

I hope your problem solve with SQl Inner Join, for more information read this sql_join_inner.asp

and

C#

SELECT Project, Godown,Product,Qty
FROM Table1 A
INNER JOIN Table2 B
ON A.Project = B.Project.id

Posted 17-Dec-13 0:32am

joginder-banger

Updated 17-Dec-13 0:33am

v2

Solution 2

SQL

SELECT
  t1.project,
  t1.godown,
  t2.product,
  t2.qty
FROM t1
LEFT JOIN t2 on t2.project =  t1.project AND t2.godown = t1.godown
ORDER BY t1.project, t1.godown

Should be your solution.

Posted 17-Dec-13 0:33am

idle63

Updated 17-Dec-13 1:03am

v3

Solution 3

try this:-

SQL

SELECT t1.project,t1.godown,t2.product,t2.qty FROM t1
INNER JOIN t2 on t1.project = t2.project AND t1.godown = t2.godown

Posted 17-Dec-13 0:42am

TrushnaK

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)

Animesh Datta · Accepted Answer · 2013-12-17T01:14:00

Hello ,

Try this Query .

create Table #tmptable  
(
project int not null ,
godown varchar(20) not null
)

insert into #tmptable values(0,11)
insert into #tmptable values(0,12)
insert into #tmptable values(1,11)
insert into #tmptable values(1,12)
insert into #tmptable values(2,11)
insert into #tmptable values(2,12)


create Table #tmptable1  
(
project int not null ,
godown varchar(20) not null,
product int not null ,
qty int not null
)
insert into #tmptable1 values(0,11,22,5)
insert into #tmptable1 values(1,12,23,6)
insert into #tmptable1 values(2,11,24,7)


select X.Project , X.GoDown,Y.product , X.Qty from
 (select a.project as Project, a.godown as GoDown, b.product as Product,
 (case when  b.qty  is null then 0 else b.qty end )Qty from #tmptable a left  outer join #tmptable1 b on a.project=b.project
  and a.godown=b.godown) X 

 inner join  

 (select  project, product from #tmptable1) Y
 on X.Project=Y.project

thanks