Join Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Join Query

Author

Topic

ravininave
Posting Yak Master

111 Posts

Posted - 2012-04-17 : 03:30:18

I have output of two queries
First Query OutPut
GDate mNo

Second Query OutPut
GDate wNo

I've to show the output like
GDate mNo wNo
How could I combine these columns

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-17 : 03:40:10

[code]
SELECT *
FROM
(
< ..Q1.. >
) as Q1
INNER JOIN
(
< ..Q2.. >
) as Q2 ON Q1.GDate = Q2.GDate
[/code]

if you have mismatch gdate, then you might need to use outer join

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-17 : 03:40:27

Is the number of rows same for both the queries? Look for Cross-tab or PIVOT

Madhivanan

Failing to plan is Planning to fail

ravininave
Posting Yak Master

111 Posts

Posted - 2012-04-17 : 04:23:43

quote:
Originally posted by khtan

SELECT *
FROM
(
< ..Q1.. >
) as Q1
INNER JOIN
(
< ..Q2.. >
) as Q2 ON Q1.GDate = Q2.GDate

if you have mismatch gdate, then you might need to use outer join

KH
[spoiler]Time is always against us[/spoiler]

Doesn't works
Both the queries comes from different tables

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

ravininave
Posting Yak Master

111 Posts

Posted - 2012-04-17 : 04:27:11

quote:
Originally posted by madhivanan

Is the number of rows same for both the queries? Look for Cross-tab or PIVOT

Madhivanan

Failing to plan is Planning to fail

I think you are correct. Should use Piot table. Can u post some links or solution for my query.

Thanx

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-17 : 05:37:55

<<
Doesn't works
Both the queries comes from different tables
>>

What di you mean by "Doesn't work?". Do both the queries return the column GDate?

Madhivanan

Failing to plan is Planning to fail

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-17 : 07:48:48

Are the GDate values returned from both the tables same??....Please post the result sets of both the queries...Life could be a lot easier if you did so.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

ravininave
Posting Yak Master

111 Posts

Posted - 2012-04-17 : 20:02:26

quote:
Originally posted by madhivanan

<<
Doesn't works
Both the queries comes from different tables
>>

What di you mean by "Doesn't work?". Do both the queries return the column GDate?

Madhivanan

Failing to plan is Planning to fail

First Query Return :
GDate mNo
2011-01-31 00:00:00 5
2011-02-01 00:00:00 15
2011-05-12 00:00:00 5
2011-05-13 00:00:00 4

Second Query Return :
GDate wNo
2011-02-01 00:00:00 3
2011-02-02 00:00:00 1
2011-05-12 00:00:00 8
2011-05-18 00:00:00 9
2011-05-22 00:00:00 6

I've to show output like

GDate mNo wNo
2011-01-31 00:00:00 5 0
2011-02-01 00:00:00 15 3
2011-05-12 00:00:00 5 8
2011-05-13 00:00:00 4 0
2011-05-18 00:00:00 9 0
2011-05-22 00:00:00 6 0

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-17 : 21:34:05

Can you also show us the query that you tried that "Doesn't works" ?

KH
[spoiler]Time is always against us[/spoiler]

ravininave
Posting Yak Master

111 Posts

Posted - 2012-04-18 : 02:37:18

quote:
Originally posted by khtan

Can you also show us the query that you tried that "Doesn't works" ?

KH
[spoiler]Time is always against us[/spoiler]

My query
SELECT *
FROM
(
Select Cast(convert(Char(11),a.LogDate,113) as datetime) as GenDate, COUNT(a.scratchno) as TotalPins
from ScratchMast a where OwnerID = 1669
and genDate >=@FirstDate and GenDate <= @SecondDate and LogDate is not null
group by Cast(convert(Char(11),a.LogDate,113) as datetime)
) as Q1
INNER JOIN
(
Select vdate as GenDate, COUNT (*) as TotalPins from IncExpMast b Where Part ='PIN FROM EWALLET' group by VDate
) as Q2 ON Q1.GenDate = Q2.GenDate

It shows the data of those dates only which are common
means if first and second query has the data of 01 jan then
only it shows data. It doesn't shows data of uncommon dates.
kindly see my output

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-18 : 03:00:53

quote:
Originally posted by khtan

if you have mismatch gdate, then you might need to use outer join

Change your join to FULL OUTER JOIN

KH
[spoiler]Time is always against us[/spoiler]

ravininave
Posting Yak Master

111 Posts

Posted - 2012-04-18 : 09:19:13

quote:
Originally posted by khtan

quote:
Originally posted by khtan

if you have mismatch gdate, then you might need to use outer join

Change your join to FULL OUTER JOIN

KH
[spoiler]Time is always against us[/spoiler]

Explain plz

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

ravininave
Posting Yak Master

111 Posts

Posted - 2012-04-18 : 09:27:03

quote:
Originally posted by khtan

quote:
Originally posted by khtan

if you have mismatch gdate, then you might need to use outer join

Change your join to FULL OUTER JOIN

KH
[spoiler]Time is always against us[/spoiler]

Oh Thanx! It solved my issue.....

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

ravininave
Posting Yak Master

111 Posts

Posted - 2012-04-19 : 01:26:43

quote:
Originally posted by khtan

quote:
Originally posted by khtan

if you have mismatch gdate, then you might need to use outer join

Change your join to FULL OUTER JOIN

KH
[spoiler]Time is always against us[/spoiler]

Now the headache is to show date in only 1 column.

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 01:34:52

use


SELECT COALESCE(Q1.GenDate,Q2.GenDate) AS GenDate,
COALESCE(Q1.TotalPins,0) + COALESCE(Q2.TotalPins,0) AS TotalPins
FROM
(
Select Cast(convert(Char(11),a.LogDate,113) as datetime) as GenDate, COUNT(a.scratchno) as TotalPins
from ScratchMast a where OwnerID = 1669 
and genDate >=@FirstDate and GenDate <= @SecondDate and LogDate is not null
group by Cast(convert(Char(11),a.LogDate,113) as datetime) 
) as Q1
FULL OUTER JOIN
(
Select vdate as GenDate, COUNT (*) as TotalPins from IncExpMast b Where Part ='PIN FROM EWALLET' group by VDate 
) as Q2 ON Q1.GenDate = Q2.GenDate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ravininave
Posting Yak Master

111 Posts

Posted - 2012-04-19 : 02:41:58

quote:

Originally posted by visakh16

use


SELECT COALESCE(Q1.GenDate,Q2.GenDate) AS GenDate,
COALESCE(Q1.TotalPins,0) + COALESCE(Q2.TotalPins,0) AS TotalPins
FROM
(
Select Cast(convert(Char(11),a.LogDate,113) as datetime) as GenDate, COUNT(a.scratchno) as TotalPins
from ScratchMast a where OwnerID = 1669 
and genDate >=@FirstDate and GenDate <= @SecondDate and LogDate is not null
group by Cast(convert(Char(11),a.LogDate,113) as datetime) 
) as Q1
FULL OUTER JOIN
(
Select vdate as GenDate, COUNT (*) as TotalPins from IncExpMast b Where Part ='PIN FROM EWALLET' group by VDate 
) as Q2 ON Q1.GenDate = Q2.GenDate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Really, Thanx a lot

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 08:50:50

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources