Retrieving data from multiple tables

In the database are two tables. One contains data that is pending some sort
of resolution; the other contains data that has been approved for further
use. Both tables contain the same attributes. When pending data is approved,
that tuple is moved to the approved table and removed from the pending table
.
If new data is to be added, the incomplete data is placed in the pending
table. Note that restrictions that apply to the approved table do not apply
to the pending table. Duplicates are not allowed in either table nor between
tables.
I need to write a query that extracts all tuples from both tables that meet
some criteria. I'm not interested in building two datasets and then
programmatically combining them. I'd like to do the task in one SQL statemen
t
if possible. I do care from which table the data is retrieved.
Thoughts?
TIA
Gus GustafsonYeah could be, if you could procide any DDL, t hard to code some SQL on just
a common description of your tasks, post it and we will help you solving
this proble,.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Gus Gustafson" <GusGustafson@.discussions.microsoft.com> schrieb im
Newsbeitrag news:F0E1C952-44B3-4DF7-9A79-A7EA99525C16@.microsoft.com...
> In the database are two tables. One contains data that is pending some
> sort
> of resolution; the other contains data that has been approved for further
> use. Both tables contain the same attributes. When pending data is
> approved,
> that tuple is moved to the approved table and removed from the pending
> table.
> If new data is to be added, the incomplete data is placed in the pending
> table. Note that restrictions that apply to the approved table do not
> apply
> to the pending table. Duplicates are not allowed in either table nor
> between
> tables.
> I need to write a query that extracts all tuples from both tables that
> meet
> some criteria. I'm not interested in building two datasets and then
> programmatically combining them. I'd like to do the task in one SQL
> statement
> if possible. I do care from which table the data is retrieved.
> Thoughts?
> TIA
> --
> Gus Gustafson|||You could create a view that cobines the two tables... Adding an expression
based attribute (column) in the process that would identify which table each
row came from
Create View TableView
As
Select 'Pend' As Source, *
From PendingTable
Union
Select 'Aprd' As Source, *
From ApprovedTable
"Gus Gustafson" wrote:

> In the database are two tables. One contains data that is pending some sor
t
> of resolution; the other contains data that has been approved for further
> use. Both tables contain the same attributes. When pending data is approve
d,
> that tuple is moved to the approved table and removed from the pending tab
le.
> If new data is to be added, the incomplete data is placed in the pending
> table. Note that restrictions that apply to the approved table do not appl
y
> to the pending table. Duplicates are not allowed in either table nor betwe
en
> tables.
> I need to write a query that extracts all tuples from both tables that mee
t
> some criteria. I'm not interested in building two datasets and then
> programmatically combining them. I'd like to do the task in one SQL statem
ent
> if possible. I do care from which table the data is retrieved.
> Thoughts?
> TIA
> --
> Gus Gustafson|||Create View TableView
As
Select 'Pend' As Source, *
From PendingTable
Union
Select 'Aprd' As Source, *
From ApprovedTable
Then you can query against that view
Select * From TableView
Where Source = 'Pend'
"Gus Gustafson" wrote:

> In the database are two tables. One contains data that is pending some sor
t
> of resolution; the other contains data that has been approved for further
> use. Both tables contain the same attributes. When pending data is approve
d,
> that tuple is moved to the approved table and removed from the pending tab
le.
> If new data is to be added, the incomplete data is placed in the pending
> table. Note that restrictions that apply to the approved table do not appl
y
> to the pending table. Duplicates are not allowed in either table nor betwe
en
> tables.
> I need to write a query that extracts all tuples from both tables that mee
t
> some criteria. I'm not interested in building two datasets and then
> programmatically combining them. I'd like to do the task in one SQL statem
ent
> if possible. I do care from which table the data is retrieved.
> Thoughts?
> TIA
> --
> Gus Gustafson|||The creation SQL looks like
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Pending_Solutions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[Pending_Solutions]
GO
CREATE TABLE [dbo].[Pending_Solutions] (
[solution_ID] [int] IDENTITY (1, 1) NOT NULL ,
[solution_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[submitted_by_ID] [int] NULL ,
[domain_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[proponent_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FA_key_ID] [int] NULL ,
[initiative_code] [bit] NOT NULL ,
[pending_code] [bit] NOT NULL ,
[delete_code] [bit] NOT NULL ,
[complementary_system_code] [bit] NOT NULL ,
[FUE_date] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[functional_area_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[POC_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POC_DSN_phone_number] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[POC_commercial_phone_number] [varchar] (32) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[approval_level_code] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[approval_date] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[program_type_name] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[initiative_type_name] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[replaces_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[other_type_text] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[image_file_name] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[approved_by_ID] [int] NULL ,
[validated_by_ID] [int] NULL ,
[solution_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[long_name_text] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[solution_issues_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[d_code] [bit] NOT NULL ,
[o_code] [bit] NOT NULL ,
[t_code] [bit] NOT NULL ,
[m_code] [bit] NOT NULL ,
[l_code] [bit] NOT NULL ,
[p_code] [bit] NOT NULL ,
[f_code] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
This CREATE statement is for the Pending_Solutions table. An equivalent
CREATE is used for the Solutions table except that restrictions for foreign
keys and non NULL fields are applied..
The search criteria desired is something like
SELECT solution_ID,
solution_name,
long_name_text AS description,
pending_code AS pending
FROM ?
WHERE domain_code LIKE @.domain_code AND
proponent_code LIKE @.proponent_code AND
functional_area_code LIKE @.functional_area_code
Gus
"Jens Sü?meyer" wrote:

> Yeah could be, if you could procide any DDL, t hard to code some SQL on ju
st
> a common description of your tasks, post it and we will help you solving
> this proble,.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Gus Gustafson" <GusGustafson@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:F0E1C952-44B3-4DF7-9A79-A7EA99525C16@.microsoft.com...
>
>|||Yes do the same thing in a stored Procedure, but you will have to code it to
handle whatever search criteria functionality the end users wish to have
available, using Srored Proc parameters with null deault values to control
the filtering...
Create Procedure Get
@.Source = null,, -- pass as 'P' 'A' or null to get both
@.ColA Integer = Null,
etc...
@.
As
Select 'Pend' As Source, *
From PendingTable
Where IsNull(@.Source, 'P') = 'P'
And ColA = IsNull(@.ColA, ColA)
And ... etc.
Union
Select 'Aprd' As Source, *
From ApprovedTable
Where IsNull(@.Source, 'A') = 'A'
And ColA = IsNull(@.ColA, ColA)
And ... etc.
"Gus Gustafson" wrote:
> OK, but there is a small difficulty: the customer will not allow me to cre
ate
> tables or views. So everything that I do must be done through SQL (usually
> stored procedures). I will pass on the view to the customer for
> implementation. In the meantime, is there another way that fits within the
se
> restrictions?
> Gus
> "CBretana" wrote:
>|||Ok, i understand what you mean, you whould to the following:

> SELECT solution_ID,
> solution_name,
> long_name_text AS description,
> pending_code AS pending
> FROM
(
Select * from Pending_Solutions
UNION
Select * from Solutions
) Subquery
> WHERE domain_code LIKE @.domain_code AND
> proponent_code LIKE @.proponent_code AND
> functional_area_code LIKE @.functional_area_code
but you should consider that the restrictions could be applied in the
Subquery for every single query to optimize th query plan, just try that one
above compared with the follwing code which evtl. could be better for
executing:

> SELECT solution_ID,
> solution_name,
> long_name_text AS description,
> pending_code AS pending
> FROM
(
Select * from Pending_Solutions

> WHERE domain_code LIKE @.domain_code AND
> proponent_code LIKE @.proponent_code AND
> functional_area_code LIKE @.functional_area_code
UNION
Select * from Solutions

> WHERE domain_code LIKE @.domain_code AND
> proponent_code LIKE @.proponent_code AND
> functional_area_code LIKE @.functional_area_code
) Subquery
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Gus Gustafson" <GusGustafson@.discussions.microsoft.com> schrieb im
Newsbeitrag news:159DCBD1-79E7-4986-9798-022D457B7285@.microsoft.com...
> The creation SQL looks like
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Pending_Solutions]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Pending_Solutions]
> GO
> CREATE TABLE [dbo].[Pending_Solutions] (
> [solution_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [solution_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [submitted_by_ID] [int] NULL ,
> [domain_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [proponent_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [FA_key_ID] [int] NULL ,
> [initiative_code] [bit] NOT NULL ,
> [pending_code] [bit] NOT NULL ,
> [delete_code] [bit] NOT NULL ,
> [complementary_system_code] [bit] NOT NULL ,
> [FUE_date] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [functional_area_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [POC_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [POC_DSN_phone_number] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [POC_commercial_phone_number] [varchar] (32) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [approval_level_code] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [approval_date] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [program_type_name] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [initiative_type_name] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [replaces_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [other_type_text] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [image_file_name] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [approved_by_ID] [int] NULL ,
> [validated_by_ID] [int] NULL ,
> [solution_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [long_name_text] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [solution_issues_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [d_code] [bit] NOT NULL ,
> [o_code] [bit] NOT NULL ,
> [t_code] [bit] NOT NULL ,
> [m_code] [bit] NOT NULL ,
> [l_code] [bit] NOT NULL ,
> [p_code] [bit] NOT NULL ,
> [f_code] [bit] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> This CREATE statement is for the Pending_Solutions table. An equivalent
> CREATE is used for the Solutions table except that restrictions for
> foreign
> keys and non NULL fields are applied..
> The search criteria desired is something like
> SELECT solution_ID,
> solution_name,
> long_name_text AS description,
> pending_code AS pending
> FROM ?
> WHERE domain_code LIKE @.domain_code AND
> proponent_code LIKE @.proponent_code AND
> functional_area_code LIKE @.functional_area_code
> Gus
> "Jens Smeyer" wrote:
>|||Sorry, didn;t edit that last right...
Create Procedure GetObjectData
@.Source = null, -- pass as 'P' 'A' or null to get both
@.ColA Integer = Null,
etc...
As
Select 'Pend' As Source, *
From PendingTable
Where IsNull(@.Source, 'P') = 'P'
And ColA = IsNull(@.ColA, ColA)
And ... etc.
Union
Select 'Aprd' As Source, *
From ApprovedTable
Where IsNull(@.Source, 'A') = 'A'
And ColA = IsNull(@.ColA, ColA)
And ... etc.
"CBretana" wrote:
> Yes do the same thing in a stored Procedure, but you will have to code it
to
> handle whatever search criteria functionality the end users wish to have
> available, using Srored Proc parameters with null deault values to contro
l
> the filtering...
> Create Procedure Get
> @.Source = null,, -- pass as 'P' 'A' or null to get both
> @.ColA Integer = Null,
> etc...
> @.
> As
> Select 'Pend' As Source, *
> From PendingTable
> Where IsNull(@.Source, 'P') = 'P'
> And ColA = IsNull(@.ColA, ColA)
> And ... etc.
> Union
> Select 'Aprd' As Source, *
> From ApprovedTable
> Where IsNull(@.Source, 'A') = 'A'
> And ColA = IsNull(@.ColA, ColA)
> And ... etc.
> "Gus Gustafson" wrote:
>|||Create Procedure GetSolutionData
@.Source = null, -- pass as 'P' 'A' or null to get both
@.ColA Integer = Null,
etc...
As
Select 'Pend' As Source, *
From PendingTable
Where IsNull(@.Source, 'P') = 'P'
And ColA = IsNull(@.ColA, ColA)
And ... etc.
Union
Select 'Aprd' As Source, *
From ApprovedTable
Where IsNull(@.Source, 'A') = 'A'
And (@.domain_code Is Null or domain_code LIKE @.domain_code)
And (@.proponent_code Is Null Or proponent_code LIKE
@.proponent_code)
And (@.functional_area_code Is Null Or
functional_area_code LIKE @.functional_area_code
"CBretana" wrote:
> Sorry, didn;t edit that last right...
>
> Create Procedure GetObjectData
> @.Source = null, -- pass as 'P' 'A' or null to get both
> @.ColA Integer = Null,
> etc...
> As
> Select 'Pend' As Source, *
> From PendingTable
> Where IsNull(@.Source, 'P') = 'P'
> And ColA = IsNull(@.ColA, ColA)
> And ... etc.
> Union
> Select 'Aprd' As Source, *
> From ApprovedTable
> Where IsNull(@.Source, 'A') = 'A'
> And ColA = IsNull(@.ColA, ColA)
> And ... etc.
>
> "CBretana" wrote:
>|||Sorry hit the enter key before I was ready..
Here is the modifed to mathc yr DDL Stored Proc...
Create Procedure GetObjectData
@.Source = null, -- pass as 'P' 'A' or null to get both
@.domain_code Char(1) = Null,
@.proponent_code VarChar(12) = Null,
@.functional_area_code VarChar(8) = Null
As
Set NoCount On
Select 'Pend' As Source, *
From Pending_Solutions
Where IsNull(@.Source, 'P') = 'P'
And (@.domain_code Is Null Or domain_code LIKE @.domain_code)
And ( @.proponent_code Is Null Or proponent_code LIKE
@.proponent_code)
And ( @.functional_area_code Is Null Or
functional_area_code LIKE @.functional_area_code)
Union
Select 'Aprd' As Source, *
From Solutions
Where IsNull(@.Source, 'A') = 'A'
And (@.domain_code Is Null Or domain_code LIKE @.domain_code)
And ( @.proponent_code Is Null Or proponent_code LIKE
@.proponent_code)
And ( @.functional_area_code Is Null Or
functional_area_code LIKE @.functional_area_code)
"Gus Gustafson" wrote:
> OK, but there is a small difficulty: the customer will not allow me to cre
ate
> tables or views. So everything that I do must be done through SQL (usually
> stored procedures). I will pass on the view to the customer for
> implementation. In the meantime, is there another way that fits within the
se
> restrictions?
> Gus
> "CBretana" wrote:
>