Joining 2 Select Statements, Using Same Table

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

General SQL Server Forums

New to SQL Server Programming

Joining 2 Select Statements, Using Same Table

Author

Topic

SyDiko
Starting Member

22 Posts

Posted - 2012-03-14 : 16:25:30

I've basically self taught myself SQL from the ground up, so forgive me if this is not the most efficient way of obtaining the results that I'm after, but if you have a better suggestion, please share!

Okay, so I'm working with 2 select statements that show this type of date.

column 1a Column 2a
Data 1a Data 2a

---------

column 1b Column 2b
Data 1b Data 2b

In each select statement, Column 1a and 1b are the exact same field. Column 2a and 2b are the same with the exception that they are representing the day in the first select statement, and week in the second.

What I want to do is 'join' column 2b in the first statement, so it looks like this:

Column 1a Column 2a Column2b
Data 1a Data 2a Data 2b

Any ideas? I know I probably need to play around with subqueries and a join, but I can't put those concepts to logic for some reason. :(

Thanks in advance for any input. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-14 : 16:33:38

you just need a join between select queries on column1a =column1b condition

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-14 : 16:36:15

something like


SELECT t1.1a,t1.2a,t2.2b
FROM (first select giving 1a,2a )t1
join (second select giving 1b,2b) t2
on t2.1b = t1.1a

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

SyDiko
Starting Member

22 Posts

Posted - 2012-03-14 : 16:39:38

quote:
Originally posted by visakh16

something like

SELECT t1.1a,t1.2a,t2.2b
FROM (first select giving 1a,2a )t1
join (second select giving 1b,2b) t2
on t2.1b = t1.1a

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

Thank you for the quick reply, I see what you're doing, let me try to apply that to my query. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-14 : 16:43:04

welcome

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

SyDiko
Starting Member

22 Posts

Posted - 2012-03-14 : 16:45:40

quote:
Originally posted by visakh16

welcome

Thank you.

Another question, from reading up on the different type of joins, I was under the impression that they were used to join 2 tables? Can they really be used to join 2 select statements querying a single view?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-14 : 17:14:28

quote:
Originally posted by SyDiko

quote:
Originally posted by visakh16

welcome

Thank you.

Another question, from reading up on the different type of joins, I was under the impression that they were used to join 2 tables? Can they really be used to join 2 select statements querying a single view?

they can
but in this case if you see i'm forming dummy tables out of queries which is known as derived table.

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

SyDiko
Starting Member

22 Posts

Posted - 2012-03-15 : 14:20:14

quote:
Originally posted by visakh16

quote:
Originally posted by SyDiko

quote:
Originally posted by visakh16

welcome

Thank you.

Another question, from reading up on the different type of joins, I was under the impression that they were used to join 2 tables? Can they really be used to join 2 select statements querying a single view?

they can
but in this case if you see i'm forming dummy tables out of queries which is known as derived table.

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

Gotcha!

I haven't figured it out yet, but something within my query is causing an error. I'm not giving up and I think with some more concentration I can get it to work.

I'll definately report back. :)

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 14:33:59

What have you written so far?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

SyDiko
Starting Member

22 Posts

Posted - 2012-03-15 : 14:55:32

X002548,

I keep starting from the drawing board, but using a few fields, I actually got the derived table to work. Just not with the query below.

Also, I kind of simplied my initial question, because actual query in question, to me is actually on the more complex side and I think that's my problem.

Here are the 2 select statements that I'm looking to join:

select

f11680,
sum(case when f6040 is NOT NULL then 1 else 0 END) as 'Today'

from all_fields where

(foldername like '%cbnf%' or foldername like '%closed%')
and datediff(d,f6040,getdate()) = '0'
and DATEPART(yy,f6040) = DATEPART(yy,getdate())
and f11680 is NOT NULL and f6040 is NOT NULL and f61 is NULL

GROUP BY f11680

select

f11680,
sum(case when f6040 is NOT NULL then 1 else 0 END) as 'Yesterday'

from all_fields where

(foldername like '%cbnf%' or foldername like '%closed%')
and datediff(d,f6040,getdate()) = '1'
and DATEPART(yy,f6040) = DATEPART(yy,getdate())
and f11680 is NOT NULL and f6040 is NOT NULL and f61 is NULL

GROUP BY f11680
ORDER BY f11680

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 15:08:24

the easiest way to group them will be


select 

f11680,
sum(case when DATEPART(dd,f6040) = DATEPART(dd,getdate())  then 1 else 0 END) as [Today],
sum(case when DATEPART(dd,f6040) = DATEPART(dd,getdate())-1  then 1 else 0 END) as [Yesterday]

from all_fields where

(foldername like '%cbnf%' or foldername like '%closed%') 
and f6040> = dateadd(dd,datediff(dd,0,getdate())-1,0)
and f6040 < dateadd(dd,datediff(dd,0,getdate())+1,0)
and f11680 is NOT NULL and f6040 is NOT NULL and f61 is NULL 

GROUP BY f11680
ORDER BY f11680

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

SyDiko
Starting Member

22 Posts

Posted - 2012-03-15 : 16:43:14

quote:
Originally posted by visakh16

the easiest way to group them will be

select

f11680,
sum(case when DATEPART(dd,f6040) = DATEPART(dd,getdate()) then 1 else 0 END) as [Today],
sum(case when DATEPART(dd,f6040) = DATEPART(dd,getdate())-1 then 1 else 0 END) as [Yesterday]

from all_fields where

(foldername like '%cbnf%' or foldername like '%closed%')
and f6040> = dateadd(dd,datediff(dd,0,getdate())-1,0)
and f6040 < dateadd(dd,datediff(dd,0,getdate())+1,0)
and f11680 is NOT NULL and f6040 is NOT NULL and f61 is NULL

GROUP BY f11680
ORDER BY f11680

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

You're kidding me, it can't be that simple!!!!! OMG, IT WORKED!!!

Gawd damn, I was so close!!!! Thanks a ton!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 16:56:27

welcome

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

SyDiko
Starting Member

22 Posts

Posted - 2012-03-16 : 09:44:06

Hmm, so I was playing around with your code Visakh, and I'm curious as to why you used 2 dateadds in the where clause?

I think I understand why you used the dateadd, but I rewrote the code and it *seems* to work like this as well:

(I've added a few more columns as well to test if the week and month would work too.)

select

f11680 as [Investor],
sum(case when DATEPART(dd,f6040) = DATEPART(dd,getdate()) then 1 else 0 END) as [Today],
sum(case when DATEPART(dd,f6040) = DATEPART(dd,getdate())-1 then 1 else 0 END) as [Yesterday],
sum(case when DATEPART(wk,f6040) = DATEPART(wk,getdate()) then 1 else 0 END) as [Week],
sum(case when DATEPART(mm,f6040) = DATEPART(mm,getdate()) then 1 else 0 END) as [Month]

from all_fields where

(foldername like '%cbnf%' or foldername like '%closed%')
and datepart(yyyy,f6040) = datepart(yyyy,getdate())
and f11680 is NOT NULL and f6040 is NOT NULL and f61 is NULL

GROUP BY f11680
ORDER BY f11680

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 10:58:58

quote:
Originally posted by SyDiko

Hmm, so I was playing around with your code Visakh, and I'm curious as to why you used 2 dateadds in the where clause?

I think I understand why you used the dateadd, but I rewrote the code and it *seems* to work like this as well:

(I've added a few more columns as well to test if the week and month would work too.)

select

f11680 as [Investor],
sum(case when DATEPART(dd,f6040) = DATEPART(dd,getdate()) then 1 else 0 END) as [Today],
sum(case when DATEPART(dd,f6040) = DATEPART(dd,getdate())-1 then 1 else 0 END) as [Yesterday],
sum(case when DATEPART(wk,f6040) = DATEPART(wk,getdate()) then 1 else 0 END) as [Week],
sum(case when DATEPART(mm,f6040) = DATEPART(mm,getdate()) then 1 else 0 END) as [Month]

from all_fields where

(foldername like '%cbnf%' or foldername like '%closed%')
and datepart(yyyy,f6040) = datepart(yyyy,getdate())
and f11680 is NOT NULL and f6040 is NOT NULL and f61 is NULL

GROUP BY f11680
ORDER BY f11680

it will work. but you will be fetching lot of unwanted data as you're apply broad filter (ie based on year value). In my case i was retrieving only required subset which is 2 days worth of data
also in my case if there's an existing index on datefield it will use it as i'm not applying function over it but in your case it wont as you've applied datepart over it making it non sargable.

anyways your above query can be better written as


select 

f11680 as [Investor],
sum(case when DATEPART(dd,f6040) = DATEPART(dd,getdate())  then 1 else 0 END) as [Today],
sum(case when DATEPART(dd,f6040) = DATEPART(dd,getdate())-1  then 1 else 0 END) as [Yesterday],
sum(case when DATEPART(wk,f6040) = DATEPART(wk,getdate())  then 1 else 0 END) as [Week],
sum(1) as [Month]

from all_fields where

(foldername like '%cbnf%' or foldername like '%closed%') 
and f6040 >= dateadd(mm,datediff(mm,0,getdate()),0)
and f6040 < dateadd(mm,datediff(mm,0,getdate())+1,0)
and f11680 is NOT NULL and  f61 is NULL 

GROUP BY f11680
ORDER BY f11680

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

SyDiko
Starting Member

22 Posts

Posted - 2012-03-16 : 11:09:04

quote:
Originally posted by visakh16

quote:
Originally posted by SyDiko

Hmm, so I was playing around with your code Visakh, and I'm curious as to why you used 2 dateadds in the where clause?

I think I understand why you used the dateadd, but I rewrote the code and it *seems* to work like this as well:

(I've added a few more columns as well to test if the week and month would work too.)

select

f11680 as [Investor],
sum(case when DATEPART(dd,f6040) = DATEPART(dd,getdate()) then 1 else 0 END) as [Today],
sum(case when DATEPART(dd,f6040) = DATEPART(dd,getdate())-1 then 1 else 0 END) as [Yesterday],
sum(case when DATEPART(wk,f6040) = DATEPART(wk,getdate()) then 1 else 0 END) as [Week],
sum(case when DATEPART(mm,f6040) = DATEPART(mm,getdate()) then 1 else 0 END) as [Month]

from all_fields where

(foldername like '%cbnf%' or foldername like '%closed%')
and datepart(yyyy,f6040) = datepart(yyyy,getdate())
and f11680 is NOT NULL and f6040 is NOT NULL and f61 is NULL

GROUP BY f11680
ORDER BY f11680

it will work. but you will be fetching lot of unwanted data as you're apply broad filter (ie based on year value). In my case i was retrieving only required subset which is 2 days worth of data
also in my case if there's an existing index on datefield it will use it as i'm not applying function over it but in your case it wont as you've applied datepart over it making it non sargable.

anyways your above query can be better written as

select

f11680 as [Investor],
sum(case when DATEPART(dd,f6040) = DATEPART(dd,getdate()) then 1 else 0 END) as [Today],
sum(case when DATEPART(dd,f6040) = DATEPART(dd,getdate())-1 then 1 else 0 END) as [Yesterday],
sum(case when DATEPART(wk,f6040) = DATEPART(wk,getdate()) then 1 else 0 END) as [Week],
sum(1) as [Month]

from all_fields where

(foldername like '%cbnf%' or foldername like '%closed%')
and f6040 >= dateadd(mm,datediff(mm,0,getdate()),0)
and f6040 < dateadd(mm,datediff(mm,0,getdate())+1,0)
and f11680 is NOT NULL and f61 is NULL

GROUP BY f11680
ORDER BY f11680

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

That makes sense, thank you!

I do aplogize for the noobie(ish) type questions, but I've learned what little sql skill that I have, from trying to rewrite horribly written code before me. So please forgive me if this is amatuerish for you.

That last thing I need to figure out, which again, I'm stumbling with is how to compute the Today, Yesterday, week, and month columns for column totals. I figured out how to do the Investor column, but the last 3 are difficult since I cannot use an alias in the compute clause. >

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 11:13:21

no worries...thats how i also learn. You need to ask and gather as much info as possible and apply it and you'll soon be proficient in it. We all have come through same path.
I really appreciate you asking the minutest of details and taking time to learn it. Feel free to post and ask any sql doubts in future

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

SyDiko
Starting Member

22 Posts

Posted - 2012-03-16 : 13:14:21

quote:
Originally posted by visakh16

no worries...thats how i also learn. You need to ask and gather as much info as possible and apply it and you'll soon be proficient in it. We all have come through same path.
I really appreciate you asking the minutest of details and taking time to learn it. Feel free to post and ask any sql doubts in future

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

Will do, and thank you for all the help. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 14:44:38

you're welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources