-- 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 ?I won't pretend to understand what you are trying to do exactly, but this
query returns the values as you wanted:
select items.art_name, SKD_NOW_QUANT, SKD_Price, dealPrice.deal_price
from items
left outer join stores
on items.art_id = stores.skd_art_id
left outer join dealPrice
join customers --this might not be right, but something like this should be
on customers.ag_Id = dealPrice.ag_id
and customers.ag_id = 2 --<--this is the variable
on items.art_id = dealPrice.art_id
Your naming conventions made it pretty difficult to follow, but because you
included the scripts I was able to build a database, use the diagrams and
see kind of what was going on. Thanks for doing that!
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Kachmaryk Yuriy" <kachya@.ua.fm> wrote in message
news:%23R1ZcTtHGHA.2320@.TK2MSFTNGP11.phx.gbl...
>
> -- 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 ?
>
dilbert1947: