Need some help with a query

Hello everyone, I have a table that is setup to record a page of web
form elements. The form elements are dynamically created. Each page
contains x number of questions. (x depends on many different things
There are 4 pages. Since we never know how many form elements are on
the page, the DB was design as such
GroupID int
PageID int
QuestionID int
FormElementID int
FormElementValue varchar(500)
The problem is when trying to grab a combination of forms to display in
a report I have to write a query like this (let's assume I am trying to
grab all records that have $5/10 years) On page 1, question 4, the
form element 1 is the dollar amount and the form element 2 is the
number of years.
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=5 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=10)
This works fine. My problem is when I have to grab all other
combinations. So let's say my client wants $5/10 years, $10/15 years,
$15/20 years and all other combinations as four distinct numbers. How
can I accomplish this? Currently I am doing this (which I know is poor
as it just looks wrong and takes forever to run.
SELECT GroupID FROM thistable WHERE GroupID NOT IN (
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=5 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=10)
AND GroupID NOT IN (
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=10 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=15)
AND GroupID NOT IN (
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=15 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=20)
Let me know if you have any ideas, thanks for your help in advance
(please note that I wrote the script above based on my real script, I
can't display the exact real code, but above is very close.night_day (night_day_8@.yahoo.com) writes:
> This works fine. My problem is when I have to grab all other
> combinations. So let's say my client wants $5/10 years, $10/15 years,
> $15/20 years and all other combinations as four distinct numbers. How
> can I accomplish this? Currently I am doing this (which I know is poor
> as it just looks wrong and takes forever to run.
> SELECT GroupID FROM thistable WHERE GroupID NOT IN (
> SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
> FormElementID=1 AND FormElementValue=5 AND GroupID IN (SELECT GroupID
> from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
> FormElementValue=10)
> AND GroupID NOT IN (
> SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
> FormElementID=1 AND FormElementValue=10 AND GroupID IN (SELECT GroupID
> from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
> FormElementValue=15)
> AND GroupID NOT IN (
> SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
> FormElementID=1 AND FormElementValue=15 AND GroupID IN (SELECT GroupID
> from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
> FormElementValue=20)
You should be able to sort this out, if you learn to master EXISTS/NOT
EXISTS. I show example with your first query to get you going:
SELECT t1.GroupID
FROM thistable t1
WHERE t1.PageID=1
and t1.QuestionID=4
AND t1.FormElementID=1
AND t1.FormElementValue=5
AND EXISTS (SELECT *
from thistable t2
WHERE t2.PageID=1
AND t2.Question_ID=4
AND t2.FormElementID=2
AND t2.FormElementValue=10
AND t1.GroupID = t2.GroupID)
The point here is that you may not need multiple subqueries, but could
then use OR conditions.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thank you for your response. I've spend some time converting my
original query to use EXISTS, I can now generate the same output using
a version running NOT EXISTS as my current NOT IN statements.
Unfortunately, I've run the 2 queries in query analyzer and the NOT
EXISTS statements takes 12 seconds whereas my current NOT IN statement
takes 7 seconds so I am not seeing any benefit from using NOT EXISTS.
My query looks like so
SELECT d.GroupID
FROM thistable d
WHERE d.PageID=1
and d.QuestionID=4
AND d.FormElementID=1
AND d.FormElementValue=5
and NOT EXISTS (SELECT * FROM thistable t1 WHERE t1.PageID=1 AND
t1.Question_ID=4 AND t1.FormElementID=1 AND FormElementValue = 5
AND EXISTS (SELECT * from thistable t2 WHERE t2.PageID=1 AND
t2.Question_ID=4 AND t2.FormElementID=2 AND FormElementValue = 10 AND
t1.GroupID = t2.GroupID) and d.GroupID=t1.GroupID)|||night_day (night_day_8@.yahoo.com) writes:
> Thank you for your response. I've spend some time converting my
> original query to use EXISTS, I can now generate the same output using
> a version running NOT EXISTS as my current NOT IN statements.
> Unfortunately, I've run the 2 queries in query analyzer and the NOT
> EXISTS statements takes 12 seconds whereas my current NOT IN statement
> takes 7 seconds so I am not seeing any benefit from using NOT EXISTS.
> My query looks like so
> SELECT d.GroupID
> FROM thistable d
> WHERE d.PageID=1
> and d.QuestionID=4
> AND d.FormElementID=1
> AND d.FormElementValue=5
> and NOT EXISTS (SELECT * FROM thistable t1 WHERE t1.PageID=1 AND
> t1.Question_ID=4 AND t1.FormElementID=1 AND FormElementValue = 5
> AND EXISTS (SELECT * from thistable t2 WHERE t2.PageID=1 AND
> t2.Question_ID=4 AND t2.FormElementID=2 AND FormElementValue = 10 AND
> t1.GroupID = t2.GroupID) and d.GroupID=t1.GroupID)
Eh, the query is not exactly trivial to understand. I feel quite
bewildered. It would help if you posted:
o CREATE TABLE statement for the table.
o The output of sp_helpindex for the table.
o Some indication of number of rows and distribution.
It could also be interesting to see the query plans. You can this
by surrounding the query in SET SHOWPLAN_ALL ON. (This will not execute
the query.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx