Professional Web Applications Themes

Fairly Complex Query - Microsoft SQL / MS SQL Server

I have two tables, one containing a list of words that I want to match and another containing a full list of words and a link field into another table. I am trying to write a query that will select all the rows from table #Words where the LinkID contains all the words in #SearchWords, for example the tables below would only return 1 row, containing LinkID 1. Obviously this cannot be done efficiently in a single query because of the relationship issue. I was toying with the idea of iterating through each row in #SearchWords and creating a temp ...

  1. #1

    Default Fairly Complex Query

    I have two tables, one containing a list of words that I want to match and
    another containing a full list of words and a link field into another table.

    I am trying to write a query that will select all the rows from table #Words
    where the LinkID contains all the words in #SearchWords, for example the
    tables below would only return 1 row, containing LinkID 1.

    Obviously this cannot be done efficiently in a single query because of the
    relationship issue. I was toying with the idea of iterating through each
    row in #SearchWords and creating a temp table with all the current results,
    effetely doing an and search but I thought I would get any opinions first.

    Any ideas?

    ------------

    -- First insert into a temp table
    create table #SearchWords(Word varchar(30) collate
    SQL_Latin1_General_CP1_CI_AS NULL)
    create table #Words(Word varchar(30) collate SQL_Latin1_General_CP1_CI_AS
    NULL, LinkID int)

    insert into #SearchWords (Word) values ('SQL')
    insert into #SearchWords (Word) values ('Server')

    insert into #Words (Word, LinkID) values ('SQL', 1)
    insert into #Words (Word, LinkID) values ('Server', 1)
    insert into #Words (Word, LinkID) values ('Tables', 1)
    insert into #Words (Word, LinkID) values ('Fields', 1)
    insert into #Words (Word, LinkID) values ('SQL', 2)
    insert into #Words (Word, LinkID) values ('Queries', 2)
    insert into #Words (Word, LinkID) values ('DTS', 2)
    insert into #Words (Word, LinkID) values ('Other', 2)
    insert into #Words (Word, LinkID) values ('Random', 2)
    insert into #Words (Word, LinkID) values ('words', 2)
    insert into #Words (Word, LinkID) values ('Other', 3)
    insert into #Words (Word, LinkID) values ('Server', 3)
    insert into #Words (Word, LinkID) values ('words', 3)

    drop table #SearchWords
    drop table #Words


    Mike Davies Guest

  2. #2

    Default Re: Fairly Complex Query

    An alternative, depending on your dataset in certain cases you may have to
    use DISTINCT as well.

    SELECT LinkID
    FROM #Words w1
    WHERE EXISTS ( SELECT *
    FROM #SearchWords w2
    WHERE w2.Word = w1.Word )
    GROUP BY LinkID
    HAVING COUNT(*) = ( SELECT COUNT(*)
    FROM #SearchWords ) ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

Similar Threads

  1. Help! Complex query
    By RuBot in forum Coldfusion Database Access
    Replies: 5
    Last Post: March 7th, 12:47 AM
  2. Help for quite complex query.
    By _andrea.l in forum MySQL
    Replies: 3
    Last Post: January 1st, 04:22 PM
  3. Presenting complex query results through datagrid
    By Bijoy Naick in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: February 24th, 08:19 PM
  4. Adding/Updating records on form with complex query
    By Steve Marsden in forum Microsoft Access
    Replies: 0
    Last Post: July 28th, 10:07 AM
  5. A complex query
    By Venkatesan M in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 1st, 02:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139