none
How to do join set of records to another set of records?

    Question

  • Hello,

    I am looking for help on learning how to join a set of records to another set of records. If you don't mind I prepare some sample scripts to build temporary tables in tempdb, some user-defined functions and code inside a commented stored procedure that you can run after creating the tables and the functions. The sample code has two different examples of variables I use to show the result I expect.

    Can you please take a look and see if you can help me find a better way to write this code?

    I would appreciate it very much, thank you.

    use tempdb
    go
    
    -- 1-
    IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.WordNo') AND OBJECTPROPERTY(id, N'IsTable') = 1)  
    BEGIN 
    	DROP TABLE dbo.WordNo
    END 
    GO
    CREATE TABLE dbo.WordNo(
    	WordNo smallint IDENTITY(1,1) NOT NULL,
    	Word varchar(30) NOT NULL,
     CONSTRAINT PK_WordNo PRIMARY KEY CLUSTERED 
    (
    	WordNo ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    CREATE NONCLUSTERED INDEX IDX_WordNo_Word ON dbo.WordNo
    (
    	Word ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO
    
    -- table possibly holding up to 5,000 words
    insert into dbo.WordNo (Word)
    select 'Aaron' union all
    select 'Abbie' union all
    select 'Abby' union all
    select 'Adam' union all
    select 'Ben' union all
    select 'Benedict' union all
    select 'Benita' union all
    select 'Benjamin' union all
    select 'Bennie' union all
    select 'Benny' union all
    select 'Benton' union all
    select 'Kay' union all
    select 'Kaye' union all
    select 'Kayla' union all
    select 'Peter' union all
    select 'Phillip' union all
    select 'aud' union all
    select 'audi' union all
    select 'audio' union all
    select 'claudio' union all
    select 'claudette'
    
    select * from dbo.WordNo
    
    
    -- 2-
    IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.Product') AND OBJECTPROPERTY(id, N'IsTable') = 1)  
    BEGIN 
    	DROP TABLE dbo.Product
    END 
    GO
    CREATE TABLE dbo.Product(
    	ProductNo int IDENTITY(1,1) NOT NULL,
    	ProductID uniqueidentifier NOT NULL,
    	Price money NOT NULL,
     CONSTRAINT PK_ProductNo PRIMARY KEY CLUSTERED 
    (
    	ProductNo ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    insert into dbo.Product (ProductID, Price)
    select NEWID(), 5.22 union all
    select NEWID(), 4 union all
    select NEWID(), 44.67 union all
    select NEWID(), 25.22 union all
    select NEWID(), 10.20 union all
    select NEWID(), 3.54 union all
    select NEWID(), 0.78 union all
    select NEWID(), 50 union all
    select NEWID(), 152 union all
    select NEWID(), 47.1 union all
    select NEWID(), 10
    
    select * from dbo.Product 
    
    
    -- 3-
    IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.ProductWord') AND OBJECTPROPERTY(id, N'IsTable') = 1)  
    BEGIN 
    	DROP TABLE dbo.ProductWord
    END 
    GO
    CREATE TABLE dbo.ProductWord(
    	ProductWordNo int IDENTITY(1,1) NOT NULL,
    	ProductNo int NOT NULL,
    	WordNo smallint NOT NULL,
     CONSTRAINT PK_ProductWordNo PRIMARY KEY CLUSTERED 
    (
    	ProductWordNo ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    CREATE NONCLUSTERED INDEX IDX_ProductWord_ProductNo_Include_WordNo ON dbo.ProductWord
    (
    	ProductNo ASC
    )
    INCLUDE ( 	[WordNo]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO
    
    
    CREATE NONCLUSTERED INDEX IDX_ProductWord_WordNo_Include_ProductNo ON dbo.ProductWord
    (
    	 WordNo ASC
    )
    INCLUDE ( 	[ProductNo]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO
    
    -- duplicate ProductNo, WordNo is allowed
    insert into dbo.ProductWord (ProductNo, WordNo)
    select 1, 1 union all
    select 1, 1 union all -- Yes this is ok, I am inserting a duplicate row.
    select 1, 9 union all
    select 1, 12 union all
    select 1, 13 union all
    select 1, 14 union all
    select 1, 16 union all
    select 2, 9 union all
    select 2, 14 union all
    select 3, 1 union all
    select 3, 2 union all
    select 3, 7 union all
    select 4, 2 union all
    select 4, 16 union all
    select 5, 12 union all
    select 6, 11 union all
    select 7, 5 union all
    select 7, 8 union all
    select 8, 1 union all
    select 8, 2 union all
    select 8, 3 union all
    select 8, 4 union all
    select 8, 5 union all
    select 9, 9 union all
    select 9, 12 union all
    select 10, 1 union all
    select 10, 5 union all
    select 10, 9 union all
    select 10, 12 union all
    select 10, 11
    
    select * from dbo.ProductWord
    
    
    
    
    use tempdb
    go
    
    IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[fn_ParseStringTSQL]') 
    				AND xtype IN (N'FN', N'IF', N'TF'))
        DROP FUNCTION [dbo].[fn_ParseStringTSQL]
    GO
    
    CREATE FUNCTION [dbo].[fn_ParseStringTSQL] (@string NVARCHAR(MAX),@separator NCHAR(1))
    RETURNS @parsedString TABLE (string NVARCHAR(MAX))
    AS 
    BEGIN
       DECLARE @position int
       SET @position = 1
       SET @string = @string + @separator
       WHILE charindex(@separator,@string,@position) <> 0
          BEGIN
             INSERT into @parsedString
             SELECT substring(@string, @position, charindex(@separator,@string,@position) - @position)
             SET @position = charindex(@separator,@string,@position) + 1
          END
         RETURN
    END
    
    
    GO
    
    
    IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[fn_get_Words]') 
    				AND xtype IN (N'FN', N'IF', N'TF'))
        DROP FUNCTION [dbo].[fn_get_Words]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE function [dbo].[fn_get_Words]
        (@ProductNo int)
        returns varchar(1000)
    as
    begin
        declare @v_RET varchar(1000)
    
    	select @v_RET = 
    	  stuff(
    		(
    		select
    		  Word + ','
    		from
    			ProductWord et
    				join WordNo t
    					on et.WordNo = t.WordNo
    		where
    			ProductNo = @ProductNo
    		for xml path('')
    		), 1, 0, ''
    	  )
    
        return substring(@v_RET, 1, len(@v_RET) - 1)
    end
    
    
    
    GO
    
    

    use tempdb
    go
    
    --IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.procGetProduct') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)  
    --BEGIN 
    --	DROP PROCEDURE dbo.procGetProduct
    --END 
    --GO
    
    /*
    exec dbo.procGetProduct @Words = 'Bennie,Kayla,Aaron'
    exec dbo.procGetProduct @Words = 'Ben,Kay,Aa'
    */
    
    --CREATE PROCEDURE dbo.procGetProduct
    --	@Words varchar(max),
    --	@Offset int,
    --	@Limit int
    --as
    --begin
    
    	declare @Offset int
    	declare @Limit int
    	set @Offset = 1
    	set @Limit = 10
    
    	declare @Words varchar(max)
    	set @Words = 'Bennie,Kayla,Aaron' -- the search has to be using the logic AND meaning all three words matches should be found below. The search is not to use the logic OR here.
    	--set @Words = 'Ben,Kay,Aa'
    
    
    	-- 1-
    	-- I will use this variable only at the end
    	declare @Words_Ordered varchar(max)
    	set @Words_Ordered = stuff((
    								select
    									',' +string 
    								from 
    									[dbo].[fn_ParseStringTSQL] (@Words, ',')  
    								order by 
    									string
    								for xml path('')), 1, 1, '')
    
    	
    	-- 2-
    	create table #tempWordNos
    	(
    		WordNo int primary key clustered,
    		SearchWord varchar(30),
    		Word varchar(30)
    	)
    
    	-- I will insert into temp table #tempWordNos all the words that are found as a string inside a string
    	-- for example if i search for the word 'aud', in theory if I had these words in the WordNo table I could have the search results return: aud, audi, audio, claudio, claudette
    	-- since the word 'aud' can be found in those words
    	insert into #tempWordNos (WordNo, SearchWord, Word)
    	select
    		WordNo
    		, s2.string
    		, Word
    	from
    		dbo.WordNo t
    			join dbo.[fn_ParseStringTSQL] (@Words, ',') s2
    				on t.Word like '%' + s2.string + '%'
    
    	-- If I am searching for 'Bennie,Kayla,Aaron' then #tempWordNos will return only 3 rows since there are only three words that match the search clause above.
    	select * from #tempWordNos
    
    
    	-- 3-
    	create table #tempProductNoSearchWord
    	(
    		ProductNo int,
    		SearchWord varchar(30)
    	)
    
    	-- I will insert into temp table #tempProductNoSearchWord all the search words that can found both in temp table #tempWordNos and joining them with live table ProductWord
    	insert into #tempProductNoSearchWord (ProductNo, SearchWord)
    	select
    		p.ProductNo
    		, t.SearchWord
    	from
    		dbo.Product p
    			join dbo.ProductWord pw
    				on p.ProductNo = pw.ProductNo
    			join #tempWordNos t
    				on pw.WordNo = t.WordNo
    	group by
    		p.ProductNo
    		, t.SearchWord
    	order by
    		p.ProductNo
    		, t.SearchWord
    
    	-- If I am searching for 'Bennie,Kayla,Aaron' then ##tempProductNoSearchWord will return only 10 rows. I use group by above so that I remove the duplicates. If I commented the group by
    	-- then 11 rows will be returned where for ProductNo = 1 there would be two Aaron Words returned instead of 1. In this case I need to remove duplicates from my result.
    	select * from #tempProductNoSearchWord
    	-- Now my goal is to return the ProductNos that have matches of all the search words. In this case only ProductNo 1 has all three matching words. This is my challenge, how do I match 
    	-- a set of values on one table to a set of values on another table. Another way to say this is I need to find all three WordNos 1, 19, 14 for any ProductNo. The ProductNo has to have all these three WordNos.
    	-- So how do I write my queries to return in this example ProductNo 1?
    
    
    	-- 4-
    	create table #tempProductNoS_Word
    	(
    		ProductNo int,
    		S_Word varchar(30)
    	)
    
    	-- Since I am not sure how to join a set of records to another set of records I chose this method. I create another temp table grouped by ProductNo and I concatenate the SearchWord values from the previous temp table.
    	insert into #tempProductNoS_Word (ProductNo, S_Word)
    	select
    		ENT.ProductNo
    		, stuff((
    				select
    					',' + t.SearchWord
    				from
    					#tempProductNoSearchWord t
    				where
    					t.ProductNo = ENT.ProductNo
    				
    				group by
    					t.ProductNo
    					, t.SearchWord
    				for xml path('')), 1, 1, '') as S_Word
    	from
    		#tempProductNoSearchWord ENT
    	group by
    		ENT.ProductNo
    	order by
    		ENT.ProductNo
    
    	-- You can see here ProductNo has now the S_Word column holding this value: 'Aaron,Bennie,Kayla'
    	select * from #tempProductNoS_Word
    
    
    	-- 5-
    	create table #tempProductNos
    	(
    		ProductNo int primary key clustered
    	)
    
    	-- Here I join my previous temp table #tempProductNoS_Word with the Product table (because in my original code I need to retrieve other columns, but in this example I removed the extra columns). And it's here
    	-- that I end up using the variable @Words_Ordered
    	insert into #tempProductNos (ProductNo)
    	select
    		t.ProductNo
    	from
    		#tempProductNoS_Word t
    			join dbo.Product e
    				on t.ProductNo = e.ProductNo
    	where
    		S_Word = @Words_Ordered;
    
    	-- Obviously here the return is one record for ProductNo = 1
    	select * from #tempProductNos;
    
    
    	-- 6-
    	-- Here in this example ProductNo = 1 will be returned and Words column will return all the Words this ProductNo has in the ProductWord table, in this case they are:
    	-- 'Aaron,Aaron,Bennie,Kay,Kaye,Kayla,Phillip'
    	WITH queryResults AS
    	(
    	SELECT 
    		e.ProductNo
    		, ProductID
    		, dbo.fn_get_Words(e.ProductNo) as Words
    		, ROW_NUMBER() OVER (ORDER BY ProductID) AS RowNum
    	FROM
    		dbo.Product e
    			join #tempProductNos
    				on e.ProductNo = #tempProductNos.ProductNo
    	)
    	SELECT
    		ProductNo
    		, ProductID
    		, Words
    		, RowNum
    	FROM queryResults
    	WHERE 
    		RowNum between @Offset and @Limit
    
    
    	-- 7-
    	drop table #tempWordNos
    	drop table #tempProductNoSearchWord
    	drop table #tempProductNoS_Word
    	drop table #tempProductNos
    
    --end
    
    /*
    -- Just to see the ProductWord table ordered by ProductNo to see the ProductNo = 1 and ProductNo = 10 Words as they are the two examples I used for my script here.
    SELECT 
    	PW.ProductWordNo
        , PW.ProductNo
        , PW.WordNo
    	, W.Word
    FROM 
    	tempdb.dbo.ProductWord PW
    		join tempdb.dbo.WordNo W
    			on PW.WordNo = W.WordNo
    order by
    	ProductNo
    
    If you uncomment this line at the top to pass different set of searchwords
    	set @Words = 'Ben,Kay,Aa'
    
    then you would get ProductNo = 10 along with ProductNo = 1 as the two records that having matching search words.
    
    I have two questions:
    	1- As you can see above I had to use multiple temp tables to get this to work. Are there any better ways to do this, basically what kind of T-SQL code I could use to
    	   be able to join matching set of rows in two tables?
    	2- I would love to find the answer for my question #1. However my second question is, is the design I chose a good one? The requirements are a Product record can contain
    	   many words, the application shows the end user in a comma separated values. I figured if I were to store the comma separated values in a single column then I would have
    	   a lot of performance problems to search for a string inside a string which itself is not a single word, rather could be multiple differents words separated/distinguished by
    	   the commas. Based on what I know about Full-Text indexing, it would not work here because I think full-text indexing works only with english words, is my recollection true?
    	   Is there another approach to create this in a different design that is good for performance? The number of records involved here are hundreds of millions of product records
    	   and hundreds of millions of ProductWord record. I estimate the maximum number of unique words is 5,000.
    
    I would appreciate any help on this matter.
    
    
    Thank you very much.
    
    */
    


    Saturday, September 14, 2013 2:03 PM

All replies

  • Did you try derived tables?

    http://www.mssqltips.com/sqlservertip/1042/using-derived-tables-to-simplify-the-sql-server-query-process/

    http://www.sqlservercentral.com/articles/DerivedTables/61388/

    Also, using CTE also helps to avoid  storing data in temp tables.

    https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, September 14, 2013 2:13 PM
  • Nothing you have done is right. You do not know what a key is. You do not research industry standards. You do not write current Standard SQL. Why do you think that IDENTITY, NEWID, etc. are used in RDBMS? That is how bad non-SQL programmers fake pointer chains!   A forum, is a bad place to try to get 1-2 years of basic education. Let's start with the awful DDL. 

    CREATE TABLE Word_List
    (word VARCHAR(30) NOT NULL PRIMARY KEY); -- real key!! not a fake pointer

    Here is how we write an INSERT today; actually for the last 20+ years. Why did you use that 1970's Sybase syntax? 

    INSERT INTO Word_List (Word)
    VALUE
    ('Aaron'), ('Abbie'), ('Abby'), ('Adam'), 
    ('Ben'), ('Benedict'), ('Benita'), ('Benjamin'), 
    ('Bennie'), ('Benny'), ('Benton'), 
    ('Kay'), ('Kaye'), ('Kayla'), 
    ('Peter'), ('Phillip'), 
    ('aud'), ('audi'), ('audio'), 
    ('claudio'), ('claudette');

    Since a table models a set, its name has to be a plural or collective name. Do you really have one Products, as you said?? DECIMAL (12, 4) is a 1970's Sybase proprietary data type that does not do correct math! Google it! Products are identified by some industry standard code. You used a physical event on one particular disk drive! WHY?? I like the GTIN (http://www.gtin.info/), so here is another correction. 

    CREATE TABLE Products
    (gtin CHAR(15) NOT NULL PRIMARY KEY,  -- real RDBMS people  use industry standards
     item_price DECIMAL (12, 4) NOT NULL);  -- tak to the accounting dept about this 

    You have no idea what a relational key is, so you use fake 1950's pointers. Here is the correct way, with keys and normalization. Do you know what normalized data is? 

    CREATE TABLE Product_Words
    (gtin CHAR(15) NOT NULL 
      REFERENCES Products (gtin)
     ON UPDATE CASCADE
     ON DELETE CASCADE, 
     word VARCHAR(35) NOT NULL
     REFERENCES Word_List (word)
     ON UPDATE CASCADE
     ON DELETE CASCADE, 
    PRIMARY KEY (gtin, word));

    See how the DRI protects data integrity and avoids extra indexing that you need in non-normalized mess? 

    >> duplicate (gtin, word) is allowed <<

    NEVER! The whole idea of databases,not just SQL, was to remove redundancy. This was a major problem in punch card and mag tape system. 

    The rest of your code gets worse. You still are writing procedural code, but SQL is a declarative language. We do not use UDFs in good SQL; this is how you old COBOL and FORTRAN programmers fake your original language. 

    Did you even know that “fn_” is a design flaw called “tibbing”, but more than that, this particular prefix comes FORTRAN II! It was inherited by BASIC in the 1960's! You are not writing SQL yet! 

    We do not write parsers. We do not write loops. We do not built strings in SQL! That was COBOL in the 1960's. If you had read just one book on SQL, you would know that what you are describing is called “relation division”; relational division is one of the eight basic operations in Codd's relational algebra. The idea is that a divisor table is used to partition a dividend table and produce a quotient or results table. The quotient table is made up of those values of one column for which a second column had all of the values in the divisor. There is a really good presentation on four ways to do this at: 

    http://www.cs.arizona.edu/people/mccann/research/divpresentation.pdf

    CREATE TABLE Search_Words
     (word CHAR(35) NOT NULL PRIMARY KEY);

    INSERT INTO Search_Words
    VALUES ('Bennie'), ('Kayla'), ('Aaron');

    SELECT DISTINCT gtin 
     FROM Product_Words AS PW1
     WHERE NOT EXISTS 
      (SELECT *
       FROM Search_Words
       WHERE NOT EXISTS 
        (SELECT *
         FROM Product_Words AS PW2
         WHERE PW1.Products = PW2.Products
         AND PW2.word = Search_Words.word));

    >> -- I will insert into temp table #tempWordNos all the words that are found as a string inside a string <<

    We do not use temp tables in good SQL. Declarative programming also does not use local variables. I can write this as a procedure with a long parameter list, but I am not sure you ar ready for that yet. 

    >> -- Since I am not sure how to join a set of records [sic] to another set of records [sic] I chose this method. <<

    Another fundamental error! Records and rows are totally different! Again, you are not writing SQL yet!\


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, September 14, 2013 6:26 PM
  • Hello Sarat,

    Thank you for providing the links, I will need to learn more on derived tables.

    Sunday, September 15, 2013 1:29 PM
  • Hello Mr. CELKO,

    I appreciate your detailed explanation on my code. I am willing to learn to write better SQL.

    >> duplicate (gtin, word) is allowed <<

    >NEVER! The whole idea of databases,not just SQL, was to remove redundancy. This was a major problem in >punch card and mag tape system.

    I understand this but if they insist to allow duplicate words for the same product, what am I supposed to do. I will try again to see if I can prevent duplicates to exist.

    I made the changes to my code but I am not certain how to now search for words inside other words.

    > I can write this as a procedure with a long parameter list, but I am not sure you ar ready for that yet.

    I am ready.

    Thank you very much.

    use tempdb
    go
    
    IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.Product_Words') AND OBJECTPROPERTY(id, N'IsTable') = 1)  
    BEGIN 
    	DROP TABLE dbo.Product_Words
    END 
    GO
    
    IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.Word_List') AND OBJECTPROPERTY(id, N'IsTable') = 1)  
    BEGIN 
    	DROP TABLE dbo.Word_List
    END 
    GO
    
    IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.Products') AND OBJECTPROPERTY(id, N'IsTable') = 1)  
    BEGIN 
    	DROP TABLE dbo.Products
    END 
    GO
    
    IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.Search_Words') AND OBJECTPROPERTY(id, N'IsTable') = 1)  
    BEGIN 
    	DROP TABLE dbo.Search_Words
    END 
    GO
    
    -- 1-
    CREATE TABLE dbo.Word_List
    (
    	Word varchar(30) NOT NULL PRIMARY KEY
    )
    
    insert into dbo.Word_List (Word)
    VALUES
     ('Aaron')
    ,('Abbie')
    ,('Abby')
    ,('Adam')
    ,('Ben')
    ,('Benedict')
    ,('Benita')
    ,('Benjamin')
    ,('Bennie')
    ,('Benny')
    ,('Benton')
    ,('Kay')
    ,('Kaye')
    ,('Kayla')
    ,('Peter')
    ,('Phillip')
    ,('aud')
    ,('audi')
    ,('audio')
    ,('claudio')
    ,('claudette');
    
    select * from dbo.Word_List
    
    
    -- 2-
    CREATE TABLE Products
     (gtin CHAR(14) NOT NULL PRIMARY KEY,  -- real RDBMS people  use industry standards
      item_price DECIMAL (12, 4) NOT NULL);  -- tak to the accounting dept about this 
     
    insert into dbo.Products (gtin, item_price)
    VALUES 
     ('6289839543535', 5.22)
    ,('5463098293342', 4)
    ,('2342388454546', 44.67)
    ,('1214376343223', 25.22)
    ,('6965984934425', 10.20)
    ,('0234823895555', 3.54)
    ,('9540555344344', 0.78)
    ,('3459843257233', 50)
    ,('2345887653221', 152)
    ,('0954532134231', 47.1)
    ,('2342386346534', 10)
    
    select * from dbo.Products 
    
    
    -- 3-
    CREATE TABLE Product_Words
     (gtin CHAR(14) NOT NULL 
       REFERENCES Products (gtin)
      ON UPDATE CASCADE
      ON DELETE CASCADE, 
      word VARCHAR(30) NOT NULL
      REFERENCES Word_List (word)
      ON UPDATE CASCADE
      ON DELETE CASCADE, 
     PRIMARY KEY (gtin, word));
     
    
    truncate table dbo.Product_Words
    insert into dbo.Product_Words (gtin, Word)
    VALUES
     ('6289839543535', 'Aaron')
    ,('6289839543535', 'Bennie')
    ,('6289839543535', 'Kay')
    ,('6289839543535', 'Kaye')
    ,('6289839543535', 'Kayla')
    ,('6289839543535', 'Phillip')
    
    ,('5463098293342', 'Bennie')
    ,('5463098293342', 'Kayla')
    
    ,('2342388454546', 'Aaron')
    ,('2342388454546', 'Abbie')
    ,('2342388454546', 'Benita')
    
    ,('1214376343223', 'Abbie')
    ,('1214376343223', 'Phillip')
    
    ,('6965984934425', 'Kay')
    
    ,('0234823895555', 'Benton')
    
    ,('9540555344344', 'Ben')
    ,('9540555344344', 'Benjamin')
    
    ,('3459843257233', 'Aaron')
    ,('3459843257233', 'Abbie')
    ,('3459843257233', 'Abby')
    ,('3459843257233', 'Adam')
    ,('3459843257233', 'Ben')
    
    ,('2345887653221', 'Bennie')
    ,('2345887653221', 'Kay')
    
    ,('0954532134231', 'Aaron')
    ,('0954532134231', 'Ben')
    ,('0954532134231', 'Bennie')
    ,('0954532134231', 'Kay')
    ,('0954532134231', 'Benton')
    
    select * from dbo.Product_Words
    
    
    
    CREATE TABLE Search_Words
      (word CHAR(30) NOT NULL PRIMARY KEY);
     
    INSERT INTO Search_Words
    	VALUES ('Bennie'), ('Kayla'), ('Aaron');
    	--VALUES ('Bennie'), ('Kayla');
    	--VALUES ('Ben'), ('Kayla');
     
    SELECT DISTINCT gtin 
      FROM Product_Words AS PW1
      WHERE NOT EXISTS 
       (SELECT *
        FROM Search_Words
        WHERE NOT EXISTS 
         (SELECT *
          FROM Product_Words AS PW2
          WHERE PW1.gtin = PW2.gtin
          AND PW2.word = Search_Words.word));
    

    • Edited by sergez Sunday, September 15, 2013 1:46 PM
    Sunday, September 15, 2013 1:44 PM