Multiple Joins

Hello,

I need to create a view which links 5 tables as follows:
I have a Header Table which is keyed on Product and Year which I want to join to a Detail Table which is keyed on Product and Year and Week. I want to see all of the rows from each table, which I think is a FULL OUTER JOIN.

I then have three subsidiary tables for Sales, Orders and Deliveries which are all keyed on Product and Year and Week - I want to join each of these tables separately to the Detail table above so that again I see all of the rows from the Detail Table, the Sales Table, the Orders Table and the Deliveries table. For any Product/YearWeek there may or may not be a row on any of the Sales, Order or Deliveries table, but there will not be any rows on these tables which are not on the Detail Table.

Can I do this in the FROM clause andnif so how, or do I need to do a series of separate SELECTs for the Sales, Orders & Deliveries table with UNION clauses.
Best regards
ColinIf there are no detail Product & Years which are not present in the Header table, then this sounds like a left outer join. The rest sound like inner joins.

If I'm understanding your intention, the following (untested) should work :

select h.*,
d.*,
s.*,
de.*
from Header h left outer join
Detail on h.product = d.product and h.year = d.year inner join
sales s on s.product = d.product and s.year = d.year and s.week = d.week inner join
Orders o on o.product = d.product and o.year = d.year and o.week = d.week inner join
Deliveries de on de.product = d.product and de.year = d.year and de.week = d.week

Perhaps I'm misunderstanding what you're wanting to see...

multiple instances of sqlservr.exe process

I have a Windows Server 2003 with SQL server 2000
installed. Whenever I view the Task Manager/Processes I
see three entires for the sqlservr.exe process. I am
thinking this is not normal and must be causing certain
performance issues I am facing. Whenever the server has
been up for a few hours the server starts getting slower.
If I reboot the server everything stays fine for a few
hours but then it starts slowing down. I have 2GB of RAM
on the server, about 20 Gigs free space on the C: drive
and about 120 Gigs free space on the data drive. All the
database files are stored on the data drive. Any help or
pointers in the right direction would be greatly
appreciated.
LakshmanHow many services whose name starts "MSSQL" do you see when you look in
Services ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Lakshman" <hlokku@.hotmail.com> wrote in message
news:2a3801c3fc98$efbaef60$a301280a@.phx.gbl...
> I have a Windows Server 2003 with SQL server 2000
> installed. Whenever I view the Task Manager/Processes I
> see three entires for the sqlservr.exe process. I am
> thinking this is not normal and must be causing certain
> performance issues I am facing. Whenever the server has
> been up for a few hours the server starts getting slower.
> If I reboot the server everything stays fine for a few
> hours but then it starts slowing down. I have 2GB of RAM
> on the server, about 20 Gigs free space on the C: drive
> and about 120 Gigs free space on the data drive. All the
> database files are stored on the data drive. Any help or
> pointers in the right direction would be greatly
> appreciated.
> Lakshman|||Hello Lakshman,
Please monitor the following.
Memory\Available Bytes (Threshold<4MB)
Memory\Pages/sec (Threshold>20)
PageFile\ % Usage (Threshold>70%)
Physical Disk\Avg. Disk Queue Length ( Threshold: > 1.5 x # of spindles )
Physical Disk\% Disk Time (Threshold: >90% )
SQLServer:Memory Manager/Total Server Memory (per instance)
Also make sure your patch level is up to date on the SQL server and the OS.
Did you install any extended stored procs?
"Lakshman" <hlokku@.hotmail.com> wrote in message
news:2a3801c3fc98$efbaef60$a301280a@.phx.gbl...
> I have a Windows Server 2003 with SQL server 2000
> installed. Whenever I view the Task Manager/Processes I
> see three entires for the sqlservr.exe process. I am
> thinking this is not normal and must be causing certain
> performance issues I am facing. Whenever the server has
> been up for a few hours the server starts getting slower.
> If I reboot the server everything stays fine for a few
> hours but then it starts slowing down. I have 2GB of RAM
> on the server, about 20 Gigs free space on the C: drive
> and about 120 Gigs free space on the data drive. All the
> database files are stored on the data drive. Any help or
> pointers in the right direction would be greatly
> appreciated.
> Lakshman|||I do not see any processes with "MSSQL" but there are 2
processes that say "sqlmangr.exe"
Lakshman.
>--Original Message--
>How many services whose name starts "MSSQL" do you see
when you look in
>Services ?
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Lakshman" <hlokku@.hotmail.com> wrote in message
>news:2a3801c3fc98$efbaef60$a301280a@.phx.gbl...
>> I have a Windows Server 2003 with SQL server 2000
>> installed. Whenever I view the Task Manager/Processes I
>> see three entires for the sqlservr.exe process. I am
>> thinking this is not normal and must be causing certain
>> performance issues I am facing. Whenever the server has
>> been up for a few hours the server starts getting
slower.
>> If I reboot the server everything stays fine for a few
>> hours but then it starts slowing down. I have 2GB of RAM
>> on the server, about 20 Gigs free space on the C: drive
>> and about 120 Gigs free space on the data drive. All the
>> database files are stored on the data drive. Any help or
>> pointers in the right direction would be greatly
>> appreciated.
>> Lakshman
>
>.
>|||He was referring to Services:
Start -> Settings -> Control Panel -> Administrative Tools -> Services
<anonymous@.discussions.microsoft.com> wrote in message
news:294a01c3fca6$83b713d0$a501280a@.phx.gbl...
> I do not see any processes with "MSSQL" but there are 2
> processes that say "sqlmangr.exe"
> Lakshman.
> >--Original Message--
> >How many services whose name starts "MSSQL" do you see
> when you look in
> >Services ?
> >
> >--
> >HTH
> >
> >Jasper Smith (SQL Server MVP)
> >
> >I support PASS - the definitive, global
> >community for SQL Server professionals -
> >http://www.sqlpass.org
> >
> >
> >"Lakshman" <hlokku@.hotmail.com> wrote in message
> >news:2a3801c3fc98$efbaef60$a301280a@.phx.gbl...
> >> I have a Windows Server 2003 with SQL server 2000
> >> installed. Whenever I view the Task Manager/Processes I
> >> see three entires for the sqlservr.exe process. I am
> >> thinking this is not normal and must be causing certain
> >> performance issues I am facing. Whenever the server has
> >> been up for a few hours the server starts getting
> slower.
> >> If I reboot the server everything stays fine for a few
> >> hours but then it starts slowing down. I have 2GB of RAM
> >> on the server, about 20 Gigs free space on the C: drive
> >> and about 120 Gigs free space on the data drive. All the
> >> database files are stored on the data drive. Any help or
> >> pointers in the right direction would be greatly
> >> appreciated.
> >> Lakshman
> >
> >
> >.
> >|||If you have multiple sqlservr.exe process showing up then you have multiple
instances of SQL Server running on this server. There are a multitude of
applications that install a copy of MSDE 2000 and use it as a data store.
If you did not purposely install the instances of SQL Server then more than
like other applications did. BackUp Exec uses MSDE, so if you have it
installed on this server that would explain one of the instances.
As Adam said, check:
Start -> Settings -> Control Panel -> Administrative Tools -> Services
You should see multiple services that start with MSSQL.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Multiple Filegroups

When dealing with a vldb, multiple filegroups are obviously a necessity from
an admin point of view.. ..but from a performance p.o.v. is there any point
at which you can have too many filegroups? - Will a high number of filegroups
on a single array actually degrade performance?
I'm using sql 2005, and I'm looking at partitioning a table, the obvious
column to partition on would mean the table would end up on 100+ filegroups..
...this would mean we would have 30+ filegroups per array and I'm worried this
would cause us more problems than benefits..
TIA BenUK
Depands on what kind of RAID structure you use/Have.
SQL2005 is Scalable enough to give you the freedom of partitioning.
you can place diff accessed table on diff Raid(if available) Static
Tables ca be on RAID 5 and/or non clustered indexes can be on a RAID5
is they re static , i they are highly updated use RAID1.
Distribution of Filegroups according to their access patterns and
appropriately using RAID's, shoule be the main concern.
Maninder
MCDBA

Multiple Filegroups

When dealing with a vldb, multiple filegroups are obviously a necessity from
an admin point of view.. ..but from a performance p.o.v. is there any point
at which you can have too many filegroups? - Will a high number of filegroups
on a single array actually degrade performance?
I'm using sql 2005, and I'm looking at partitioning a table, the obvious
column to partition on would mean the table would end up on 100+ filegroups..
..this would mean we would have 30+ filegroups per array and I'm worried this
would cause us more problems than benefits..
TIA BenUKDepands on what kind of RAID structure you use/Have.
SQL2005 is Scalable enough to give you the freedom of partitioning.
you can place diff accessed table on diff Raid(if available) Static
Tables ca be on RAID 5 and/or non clustered indexes can be on a RAID5
is they re static , i they are highly updated use RAID1.
Distribution of Filegroups according to their access patterns and
appropriately using RAID's, shoule be the main concern.
Maninder
MCDBA

Multiple Filegroups

When dealing with a vldb, multiple filegroups are obviously a necessity from
an admin point of view.. ..but from a performance p.o.v. is there any point
at which you can have too many filegroups? - Will a high number of filegroup
s
on a single array actually degrade performance?
I'm using sql 2005, and I'm looking at partitioning a table, the obvious
column to partition on would mean the table would end up on 100+ filegroups.
.
..this would mean we would have 30+ filegroups per array and I'm worried th
is
would cause us more problems than benefits..
TIA BenUKDepands on what kind of RAID structure you use/Have.
SQL2005 is Scalable enough to give you the freedom of partitioning.
you can place diff accessed table on diff Raid(if available) Static
Tables ca be on RAID 5 and/or non clustered indexes can be on a RAID5
is they re static , i they are highly updated use RAID1.
Distribution of Filegroups according to their access patterns and
appropriately using RAID's, shoule be the main concern.
Maninder
MCDBA

Multiple data sources in a report model

I've built several data view sources and report models and think its great.
But this week I'v ran into problems. I'm trying to implementent a report
model that uses data from three databases. I've managed to build the data
source view containing tables from all three different data sources, but when
I try to build the report model it fails. It seems that it can't find tables
in other data sources that the primary.In SQL Server 2005, a report model can have only one data source file, one
data source view file, and one smdl file. What you are attempting to do is
not a supported scenario in this release.
"Erik Hedlund" wrote:
> I've built several data view sources and report models and think its great.
> But this week I'v ran into problems. I'm trying to implementent a report
> model that uses data from three databases. I've managed to build the data
> source view containing tables from all three different data sources, but when
> I try to build the report model it fails. It seems that it can't find tables
> in other data sources that the primary.|||Hmm, this is very limiting, because the data source view can contain tables
from several datasources. It's rather odd that the report model should be
concerned about lies bellow the data source view abstraction layer.
"Katmac [MS]" wrote:
> In SQL Server 2005, a report model can have only one data source file, one
> data source view file, and one smdl file. What you are attempting to do is
> not a supported scenario in this release.
> "Erik Hedlund" wrote:
> > I've built several data view sources and report models and think its great.
> > But this week I'v ran into problems. I'm trying to implementent a report
> > model that uses data from three databases. I've managed to build the data
> > source view containing tables from all three different data sources, but when
> > I try to build the report model it fails. It seems that it can't find tables
> > in other data sources that the primary.|||Multiple data source support is on the wish list for a future release.
"Erik Hedlund" wrote:
> Hmm, this is very limiting, because the data source view can contain tables
> from several datasources. It's rather odd that the report model should be
> concerned about lies bellow the data source view abstraction layer.
> "Katmac [MS]" wrote:
> > In SQL Server 2005, a report model can have only one data source file, one
> > data source view file, and one smdl file. What you are attempting to do is
> > not a supported scenario in this release.
> >
> > "Erik Hedlund" wrote:
> >
> > > I've built several data view sources and report models and think its great.
> > > But this week I'v ran into problems. I'm trying to implementent a report
> > > model that uses data from three databases. I've managed to build the data
> > > source view containing tables from all three different data sources, but when
> > > I try to build the report model it fails. It seems that it can't find tables
> > > in other data sources that the primary.|||If the product doesn't allow a DSV to have multiple data sources, then you
shouldn't be able to save a DSV that references more than 1 DS. I just
wasted three hours trying to track down this "bug." Certainly would have
saved me time had VS just told me it didn't support more than 1 data source
in a dsv. instead, I got a cryptic message at model generation time that it
coudln't find a table.
"Katmac [MS]" wrote:
> Multiple data source support is on the wish list for a future release.
> "Erik Hedlund" wrote:
> > Hmm, this is very limiting, because the data source view can contain tables
> > from several datasources. It's rather odd that the report model should be
> > concerned about lies bellow the data source view abstraction layer.
> >
> > "Katmac [MS]" wrote:
> >
> > > In SQL Server 2005, a report model can have only one data source file, one
> > > data source view file, and one smdl file. What you are attempting to do is
> > > not a supported scenario in this release.
> > >
> > > "Erik Hedlund" wrote:
> > >
> > > > I've built several data view sources and report models and think its great.
> > > > But this week I'v ran into problems. I'm trying to implementent a report
> > > > model that uses data from three databases. I've managed to build the data
> > > > source view containing tables from all three different data sources, but when
> > > > I try to build the report model it fails. It seems that it can't find tables
> > > > in other data sources that the primary.