HELP! Problem with data selection.

Hi,
People, help me with problem to make the query qExpectedRESULT
I will accept any suggestion and suppositions!
Possibly use of User Defined Functions it is a right way ?
-- START of DB Objects CREATE scripts --
CREATE TABLE [dbo].[Customers] (
[AG_ID] [int] IDENTITY (1, 1) NOT NULL ,
[AG_TYPE] [tinyint] NULL ,
[AG_STATE] [tinyint] NULL ,
[AG_CODE] [smallint] NULL ,
[AG_REG_NO] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[AG_REG_NAME] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[AG_REG_DATE] [datetime] NULL ,
[AG_PRINT_NAME] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[AG_SEARCH_NAME] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[AG_CR_DATE] [datetime] NULL ,
[AG_MD_DATE] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DealPRICE] (
[DP_ID] [int] IDENTITY (1, 1) NOT NULL ,
[AG_ID] [int] NULL ,
[ART_ID] [int] NULL ,
[DP_DATE] [datetime] NULL ,
[DEAL_PRICE] [money] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Items] (
[ART_ID] [int] IDENTITY (1, 1) NOT NULL ,
[ART_TYPE] [tinyint] NULL ,
[ART_STATE] [tinyint] NULL ,
[ART_FOLDER_ID] [int] NULL ,
[ART_MSK_ID] [int] NULL ,
[ART_DIN_ID] [int] NULL ,
[ART_LEVEL] [tinyint] NULL ,
[ART_INDEX] [smallint] NULL ,
[ART_NO] [varchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[ART_NAME] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
[ART_V1] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V2] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V3] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V4] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V5] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_CR_DATE] [datetime] NULL ,
[ART_MD_DATE] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemsDIN] (
[DIN_ID] [int] IDENTITY (1, 1) NOT NULL ,
[DIN_TYPE] [tinyint] NULL ,
[DIN_INDEX] [tinyint] NULL ,
[DIN_GROUP] [int] NULL ,
[DIN_NAME] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
[DIN_ALTER] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DIN_TEXT] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DIN_TEXT_STR] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DIN_PRICE_TYPE] [tinyint] NULL ,
[DIN_PRICE_UP] [money] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemsMASK] (
[MSK_ID] [int] IDENTITY (1, 1) NOT NULL ,
[MSK_TYPE] [tinyint] NULL ,
[MSK_INDEX] [smallint] NULL ,
[MSK_MAIN] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[MSK_DESCRIPTION] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
[MSK_MASK] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[MSK_PART1] [int] NULL ,
[MSK_PART2] [int] NULL ,
[MSK_PART3] [int] NULL ,
[MSK_PART4] [int] NULL ,
[MSK_PART5] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Stores] (
[SKD_ID] [int] IDENTITY (1, 1) NOT NULL ,
[SKD_TYPE] [tinyint] NULL ,
[SKD_STATE] [tinyint] NULL ,
[SKD_ART_ID] [int] NULL ,
[SKD_UPDATED] [bit] NULL ,
[SKD_NOW_QUANT] [money] NULL ,
[SKD_NOW_REZRV] [money] NULL ,
[SKD_NOW_PREP] [money] NULL ,
[SKD_NOW_UNREG] [money] NULL ,
[SKD_NOW_MOD] [money] NULL ,
[SKD_NOW_NED] [money] NULL ,
[SKD_LIMIT_MIN] [money] NULL ,
[SKD_LIMIT_MAX] [money] NULL ,
[SKD_PRICE] [money] NULL ,
[SKD_LAST_SALE] [datetime] NULL ,
[SKD_CHG_DATE] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[AG_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DealPRICE] WITH NOCHECK ADD
CONSTRAINT [PK_DealPRICE] PRIMARY KEY CLUSTERED
(
[DP_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Items] WITH NOCHECK ADD
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED
(
[ART_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ItemsDIN] WITH NOCHECK ADD
CONSTRAINT [PK_ItemsDIN] PRIMARY KEY CLUSTERED
(
[DIN_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ItemsMASK] WITH NOCHECK ADD
CONSTRAINT [PK_ItemsMASK] PRIMARY KEY CLUSTERED
(
[MSK_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Stores] WITH NOCHECK ADD
CONSTRAINT [PK_Stores] PRIMARY KEY CLUSTERED
(
[SKD_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DealPRICE] ADD
CONSTRAINT [FK_DealPRICE_Customers] FOREIGN KEY
(
[AG_ID]
) REFERENCES [dbo].[Customers] (
[AG_ID]
),
CONSTRAINT [FK_DealPRICE_Items] FOREIGN KEY
(
[ART_ID]
) REFERENCES [dbo].[Items] (
[ART_ID]
)
GO
ALTER TABLE [dbo].[Items] ADD
CONSTRAINT [FK_Items_ItemsDIN] FOREIGN KEY
(
[ART_DIN_ID]
) REFERENCES [dbo].[ItemsDIN] (
[DIN_ID]
),
CONSTRAINT [FK_Items_ItemsMASK] FOREIGN KEY
(
[ART_MSK_ID]
) REFERENCES [dbo].[ItemsMASK] (
[MSK_ID]
)
GO
ALTER TABLE [dbo].[Stores] ADD
CONSTRAINT [FK_Stores_Items] FOREIGN KEY
(
[SKD_ART_ID]
) REFERENCES [dbo].[Items] (
[ART_ID]
)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.qBaseQUERY
AS
SELECT dbo.Items.*, dbo.Stores.*, dbo.ItemsDIN.DIN_NAME AS DIN_NAME,
dbo.ItemsMASK.MSK_PART1 AS MSK_PART1,
dbo.ItemsMASK.MSK_PART2 AS MSK_PART2,
dbo.ItemsMASK.MSK_PART3 AS MSK_PART3, dbo.ItemsMASK.MSK_PART4 AS MSK_PART4,
dbo.ItemsMASK.MSK_PART5 AS MSK_PART5
FROM dbo.Items LEFT OUTER JOIN
dbo.ItemsDIN ON dbo.Items.ART_DIN_ID =
dbo.ItemsDIN.DIN_ID LEFT OUTER JOIN
dbo.ItemsMASK ON dbo.Items.ART_MSK_ID =
dbo.ItemsMASK.MSK_ID LEFT OUTER JOIN
dbo.Stores ON dbo.Items.ART_ID = dbo.Stores.SKD_ART_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.qExpectedRESULT
AS
SELECT ART_ID, ART_NAME, SKD_NOW_QUANT, SKD_PRICE, 'from DealPRICE
table' AS DEAL_PRICE
FROM dbo.qBaseQUERY
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- END of DB Objects CREATE scripts --
-- Fill Tables ----
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerA','CustomerA','Customer
A')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerB','CustomerB','Customer
B')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerC','CustomerC','Customer
C')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerD','CustomerD','Customer
D')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerE','CustomerE','Customer
E')
INSERT INTO Items(ART_NAME) VALUES('ItemA')
INSERT INTO Items(ART_NAME) VALUES('ItemB')
INSERT INTO Items(ART_NAME) VALUES('ItemC')
INSERT INTO Items(ART_NAME) VALUES('ItemD')
INSERT INTO Items(ART_NAME) VALUES('ItemE')
INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRIC
E) VALUES(1,453,10.95)
INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRIC
E) VALUES(3,675,15.95)
INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRIC
E) VALUES(5,134,20.95)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(1,1,GETDATE(),10.55)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(1,2,GETDATE(),13)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(1,3,GETDATE(),13.5)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(2,3,GETDATE(),14.3)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(3,4,GETDATE(),15)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(4,5,GETDATE(),18.9)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(5,5,GETDATE(),19.1)
-- END of Fill Tables ---
Query qExpectedRESULT for Customers.AG_ID=1 must return the next list:
1 ItemA 453 10,95 10,55
2 ItemB NULL NULL 13
3 ItemC 675 15,95 13,5
4 ItemD NULL NULL NULL
5 ItemE 134 20,95 NULL
for Customers.AG_ID=2:
1 ItemA 453 10,95 NULL
2 ItemB NULL NULL NULL
3 ItemC 675 15,95 14.3
4 ItemD NULL NULL NULL
5 ItemE 134 20,95 NULL
In a real DB like:
Customers - 30 000 rows
Items - 25 000 rows
Stores - 15 000 rows
DealPRICE - 1 000 rows
im not select all 25000 rows:
SELECT qBaseQUERY.* -- base query
FROM qBaseQUERY -- about 25 000 rows
next text added on clients terminals depended on their needs, like:
WHERE qBaseQUERY.ART_MSK_ID=13 -- return 10...30 rows
AND ((((qBaseQUERY.MSK_PART1) = 1))
AND (((qBaseQUERY.ART_V1) = '100')))
AND ((((qBaseQUERY.MSK_PART2) = 3))
AND (((qBaseQUERY.ART_V2) = '050')))
AND ((((qBaseQUERY.MSK_PART3) = 12))
AND (((qBaseQUERY.ART_V3) = '058')))
AND ((((qBaseQUERY.MSK_PART4) = 11))
AND (((qBaseQUERY.ART_V4) = '001')))
AND ((((qBaseQUERY.MSK_PART5) = 36))
AND (((qBaseQUERY.ART_V5) = '105')))
AND (((qBaseQUERY.SKD_NOW_QUANT)>0) OR ((qBaseQUERY.SKD_NOW_UNREG)>0) )
ORDER BY qBaseQUERY.ART_FOLDER_ID, qBaseQUERY.ART_LEVEL,
qBaseQUERY.ART_INDEX
--
What do you think about it ?"Kachmaryk Yuriy" <kachya@.ua.fm> wrote in message
news:O39Wii2HGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> People, help me with problem to make the query qExpectedRESULT
> I will accept any suggestion and suppositions!
> Possibly use of User Defined Functions it is a right way ?
>
How about
SELECT B.ART_ID, B.ART_NAME, B.SKD_NOW_QUANT, B.SKD_PRICE, DP.DEAL_PRICE
FROM dbo.qBaseQUERY B
LEFT JOIN dbo.dealPRICE DP
ON B.ART_ID = DP.ART_ID
and AG_ID = 1
?
David