|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Need the Guru HELP. Ðåáÿòà, ïîìîãèòå!Teble #1 - CUSTOMER ----------------------------------------------- Show quote |---CustomerID---|---CustomerCODE---|---CustomerNAME---| 30 000 rows|----------------------|---------------------------|------------------------ ----| | 1 | 01020304 | CustomerA | | ... | ... | ... | | ... | ... | ... | | 241 | 10203040 | CustomerAAA | | 242 | 10203042 | CustomerAAB | | ... | ... | ... | | ... | ... | ... | | 30000 | 98784560 | CustomerAABCD | ---------------------------------------------------------------------------- --- Teble #2 - PRICE ----------------------------------- Show quote |---ItemID---|---ItemNAME---|---ItemPRICE---| 25 000 rows|---------------|---------------------|--------------------| | 1 | ItemA0001 | 25.25 | | ... | ... | ... | | ... | ... | ... | | 5674 | ItemA1101 | 55.55 | | 5675 | ItemA1102 | 45.85 | | ... | ... | ... | | ... | ... | ... | | 25000 | ItemA1B01 | 35.25 | ---------------------------------------------------------- Teble #3 - DEAL_PRICE |---CustomerID---|---ItemID---|---DealPRISE---| Expect Results for CustomerID=241:|----------------------|---------------|--------------------| | 241 | 5674 | 53.50 | | 241 | 5675 | 40.50 | | 241 | 1 | 23.50 | | 242 | 5675 | 43.50 | | 242 | 25000 | 33.50 | ----------------------------------------------------------- 5 rows Show quote |---ItemID---|---ItemNAME---|---ItemPRICE---|---DealPRISE---| 25 000 rows|---------------|---------------------|---------------------|--------------- -----| | 1 | ItemA0001 | 25.25 | 23.50 | | ... | ... | ... | NULL | | ... | ... | ... | NULL | | 5674 | ItemA1101 | 55.55 | 53.50 | | 5675 | ItemA1102 | 45.85 | 40.50 | | ... | ... | ... | NULL | | ... | ... | ... | NULL | | 25000 | ItemA1B01 | 35.25 | NULL | ---------------------------------------------------------------------------- --- Thanks to anyone who could help. select case when customerId is null then '...' else cast(price.itemId as
varchar(10)) end as itemId, case when customerId is null then '...' else cast(price.itemName as varchar(10)) end as itemName, case when customerId is null then '...' else cast(price.itemPrice as varchar(10)) end as itemPrice, case when customerId is null then NULL else cast(dealPrice.dealPrice as varchar(10)) end as dealPrice from price left outer join dealPrice join customer --this might not be right, but something like this should be on customer.customerId = dealPrice.customerId and customerId = 241 on dealPrice.itemId = price.itemId I have no idea why you want to get back 25000 - 4 rows of blank rows, but I think this query might work. Give us table structures with data (that can be executed in SQL ) and I could be sure. These structures themselves are a bit concerning, but not half as much as 25000 rows of blanks... -- Show quote---------------------------------------------------------------------------- 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" <kac***@ua.fm> wrote in message news:%23NMdB1gHGHA.2704@TK2MSFTNGP15.phx.gbl... >I have 3 tables: > > Teble #1 - CUSTOMER ----------------------------------------------- > |---CustomerID---|---CustomerCODE---|---CustomerNAME---| > |----------------------|---------------------------|------------------------ > ----| > | 1 | 01020304 | CustomerA > | > | ... | ... | ... > | > | ... | ... | ... > | > | 241 | 10203040 | CustomerAAA > | > | 242 | 10203042 | CustomerAAB > | > | ... | ... | ... > | > | ... | ... | ... > | > | 30000 | 98784560 | CustomerAABCD > | > ---------------------------------------------------------------------------- > --- > 30 000 rows > > > Teble #2 - PRICE ----------------------------------- > |---ItemID---|---ItemNAME---|---ItemPRICE---| > |---------------|---------------------|--------------------| > | 1 | ItemA0001 | 25.25 | > | ... | ... | ... > | > | ... | ... | ... > | > | 5674 | ItemA1101 | 55.55 | > | 5675 | ItemA1102 | 45.85 | > | ... | ... | ... > | > | ... | ... | ... > | > | 25000 | ItemA1B01 | 35.25 | > ---------------------------------------------------------- > 25 000 rows > > > Teble #3 - DEAL_PRICE > |---CustomerID---|---ItemID---|---DealPRISE---| > |----------------------|---------------|--------------------| > | 241 | 5674 | 53.50 | > | 241 | 5675 | 40.50 | > | 241 | 1 | 23.50 | > | 242 | 5675 | 43.50 | > | 242 | 25000 | 33.50 | > ----------------------------------------------------------- > 5 rows > > > Expect Results for CustomerID=241: > > |---ItemID---|---ItemNAME---|---ItemPRICE---|---DealPRISE---| > |---------------|---------------------|---------------------|--------------- > -----| > | 1 | ItemA0001 | 25.25 | 23.50 > | > | ... | ... | ... > | NULL | > | ... | ... | ... > | NULL | > | 5674 | ItemA1101 | 55.55 | 53.50 > | > | 5675 | ItemA1102 | 45.85 | 40.50 > | > | ... | ... | ... > | NULL | > | ... | ... | ... > | NULL | > | 25000 | ItemA1B01 | 35.25 | NULL > | > ---------------------------------------------------------------------------- > --- > 25 000 rows > > Thanks to anyone who could help. > > -- 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','CustomerA') INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME) VALUES('CustomerB','CustomerB','CustomerB') INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME) VALUES('CustomerC','CustomerC','CustomerC') INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME) VALUES('CustomerD','CustomerD','CustomerD') INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME) VALUES('CustomerE','CustomerE','CustomerE') 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_PRICE) VALUES(1,453,10.95) INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRICE) VALUES(3,675,15.95) INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRICE) VALUES(5,134,20.95) INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRICE) VALUES(1,1,GETDATE(),10.55) INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRICE) VALUES(1,2,GETDATE(),13) INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRICE) VALUES(1,3,GETDATE(),13.5) INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRICE) VALUES(2,3,GETDATE(),14.3) INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRICE) VALUES(3,4,GETDATE(),15) INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRICE) VALUES(4,5,GETDATE(),18.9) INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRICE) 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! -- Show quote---------------------------------------------------------------------------- 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" <kac***@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','CustomerA') > INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME) > VALUES('CustomerB','CustomerB','CustomerB') > INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME) > VALUES('CustomerC','CustomerC','CustomerC') > INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME) > VALUES('CustomerD','CustomerD','CustomerD') > INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME) > VALUES('CustomerE','CustomerE','CustomerE') > > 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_PRICE) VALUES(1,453,10.95) > INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRICE) VALUES(3,675,15.95) > INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRICE) VALUES(5,134,20.95) > > INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRICE) > VALUES(1,1,GETDATE(),10.55) > INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRICE) > VALUES(1,2,GETDATE(),13) > INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRICE) > VALUES(1,3,GETDATE(),13.5) > INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRICE) > VALUES(2,3,GETDATE(),14.3) > INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRICE) > VALUES(3,4,GETDATE(),15) > INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRICE) > VALUES(4,5,GETDATE(),18.9) > INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRICE) > 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 ? > >  |
|||||||||||||||||||||||
Other interesting topics