Select based on results of another select

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

Select based on results of another select

Author

Topic

dibblejon
Starting Member

36 Posts

Posted - 2012-06-27 : 09:22:40

Hi

We have two tables and I would like to try and create some form of lookup between the two - but am having issues.

Table syscontrol

SQL

select * from syscontrol
where keyfield = 316 or keyfield = 317

316;1;0;"";"Sales";1;0;"11";"Period";"Period";0
317;1;0;"";"Sales";2;0;"2012";"Year";"Year";0

Table ststats

Results from simple query

124108;1;"01";"TAN0018";1;2012
124109;1;"01";"TAN0018";2;2012
124110;1;"01";"TAN0018";4;2012
124111;1;"01";"TAN0018";6;2012
124112;1;"01";"CTS5001";1;2012
124113;1;"01";"CTS5001";2;2012
124114;1;"01";"CTS5001";3;2012
124115;1;"01";"CTS5001";5;2012
124116;1;"01";"CTS5001";6;2012
124117;1;"01";"JOH0610";1;2012
124118;1;"01";"JOH0610";2;2012
124119;1;"01";"JOH0610";3;2012
124120;1;"01";"JOH0610";4;2012
124121;1;"01";"JOH0610";5;2012
124122;1;"01";"JOH0610";6;2012

What I want to try and achieve is one query that has logic like:

Select results from ststats where results of query on syscontrol table for Period and Year equal current Year and Period (which is what is returned in the 1st query) and show also show the preceding 6 months.

So for example if the Period is 7 and the Year is 2012 I would like to see froms ststats all records that have 7,2012 6,2012 5,2012 and so on

Any help would be fantastic

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-27 : 10:09:11

You can use a cte to reference a derived table
;with t1 as
(
select ...
) ,
t2 as
(
select ...
)
select *
from t1 join t2
on ...

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dibblejon
Starting Member

36 Posts

Posted - 2012-06-27 : 10:47:28

Thanks will try this

Had also tried this but is falling over

SELECT 
  ststats.company, 
  ststats.depot, 
  ststats.part, 
  ststats.sale_period, 
  ststats.sale_year, 
  ststats.units, 
  "CurrentPeriod".notes, 
  "CurrentPeriod".code_value::smallint AS "CurrentPeriod", 
  "CurrentYear".notes, 
  "CurrentYear".code_value::smallint AS "CurrentYear"
FROM 
  public.ststats, 
  public."CurrentPeriod", 
  public."CurrentYear"
WHERE 
  ststats.company = "CurrentPeriod".company AND
  ststats.company = "CurrentYear".company AND
  ststats.sale_year >= '2011' AND
  public.CurrentPeriod.code_value = sale_period -1

With this error - where I am going wrong?

ERROR: missing FROM-clause entry for table "currentperiod"
LINE 20: public.CurrentPeriod.code_value = sale_period -1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources