Help with Sql Select statement
-
Hi ppl,
I need a little help with a sql statement
I have 8 fields like this q1a,q1b,q1c,q1d,q2a,q2b,q2c,q2d
each field is int()
values will be from 1 to 4
I need to only return a result set if 2 of the q1's values are greater than 2 or 2 of the q2's values are greater than 2
so
Select * from mytable where ???
Thanks in advance
-
I am little tired but this may get you started. Considering You Have NO NULL NEITHER 0 in the q's you can:
SELECT * FROM MYTable
Where
( q1a*q1b*q1c*q1d > 2)>1
OR
( q2a*q2b*q2c*q2d > 2)>1
HTH
* Noel -
I forgot that part there may be 0's if they question was not answered..
-
Disregard my previous answer, (I am tired
)Along as you don't have nulls you can:
SELECT * FROM MYTable
Where
( (q1a/3) + (q1b/3 ) + (q1c /3) + (q1d/3) > =2)
OR
((q2a/3) + (q2b/3 ) + (q2c /3) + (q2d/3) > = 2)
If you want to handle nulls as well then you can repace each (q1x/3) for ISNULL(q1x/3,0) and it will take care of it
gotta go home now
Edited by - noeld on 12/22/2003 5:15:41 PM
Edited by - noeld on 12/22/2003 5:16:32 PM
* Noel -
A minor refinement of noeld's excellent second suggestion:
SELECT
q1a, q1b, q1c, q1d
, q2a, q2b, q2c, q2d
FROM mytable
WHERE
CASE
WHEN ( q1a + q1b + q1c + q1d ) / 4 > 1 THEN 1
WHEN (q2a + q2b + q2c + q2d ) / 4 > 1 THEN 1
ELSE 0 END > 0
Edited by - Mongo_ks on 12/22/2003 11:12:29 PM
-
quote:
A minor refinement of noeld's excellent second suggestion:SELECT
q1a, q1b, q1c, q1d
, q2a, q2b, q2c, q2d
FROM mytable
WHERE
CASE
WHEN ( q1a + q1b + q1c + q1d ) / 4 > 1 THEN 1
WHEN (q2a + q2b + q2c + q2d ) / 4 > 1 THEN 1
ELSE 0 END > 0
Edited by - Mongo_ks on 12/22/2003 11:12:29 PM
I don't think that works. Division HAS TO COME FIRST!
Eg:
q1a = 1, q1b = 1, q1c = 1, q1d = 3
will produce the same result that
q1a = 0, q1b = 0, q1c = 3, q1d = 3
and the first one has to be rejected but NOT the second one.
* Noel -
I'm uncomfortable with this problem for a few reasons. First, this data is obviously not even in first normal form. This information should be in two tables, not one.
Second, the columns should be tinyint, not int.
Third, while Noel's query will return the correct result set, this sort of thing is very dependent upon the data, i.e. this method will not work if the valid values are changed even slightly, e.g. allow values of 0 to 5. Mongo's solution fails if a group contains two zeros and two threes or a group contains all twos.
My persnickety but extensible answer would be to normalize the schema and then use relational division. If you're locked into the denormalized table, then you can normalize using a derived table, e.g.:
SELECT t.*
FROM MyTable t JOIN
(SELECT DISTINCT Id
FROM
(SELECT Id, 1 q, 'a' c, Q1a a
FROM MyTable
UNION ALL
SELECT Id, 1, 'b', q1b
FROM MyTable
UNION ALL
SELECT Id, 1, 'c', q1c
FROM MyTable
UNION ALL
SELECT Id, 1, 'd', q1d
FROM MyTable
UNION ALL
SELECT Id, 2, 'a', q2a
FROM MyTable
UNION ALL
SELECT Id, 2, 'b', q2b
FROM MyTable
UNION ALL
SELECT Id, 2, 'c', q2c
FROM MyTable
UNION ALL
SELECT Id, 2, 'd', q2d
FROM MyTable) x
WHERE a > 2
GROUP BY Id, q
HAVING COUNT(*) > 1) y ON y.Id = t.IdWhere Id is the primary key of MyTable.
--Jonathan
--Jonathan -
I found the answer. The reason the data is what it is is because I am tying into our survey software's SQL table, So I have no control over its structure. But here is the answer to this problem
SELECT * FROM mytable
WHERE
(
(CASE WHEN q8a>2 THEN 1 ELSE 0 END) +
(CASE WHEN q8b>2 THEN 1 ELSE 0 END) +
(CASE WHEN q8c>2 THEN 1 ELSE 0 END) +
(CASE WHEN q8d>2 THEN 1 ELSE 0 END) ) >=2
OR (
(CASE WHEN q9a>2 THEN 1 ELSE 0 END) +
(CASE WHEN q9b>2 THEN 1 ELSE 0 END) +
(CASE WHEN q9c>2 THEN 1 ELSE 0 END) +
(CASE WHEN q9d>2 THEN 1 ELSE 0 END) ) >=2
-
I Believe I have to join Frank Kalis club of new words persnickety
* About the Normalization Aspect of the problem I concur with it 100%
I just tried solve the problem
* Noel -
I agree also with the normalization aspect as well.
Unfortunatly sometimes in the real world (or at least my world ) we get handed a probelem and have to deal with it. And I appreciate all the people who really try to help solve it. and to the rest I enjoy learning new words 🙂
-
quote:
I agree also with the normalization aspect as well.Unfortunatly sometimes in the real world (or at least my world ) we get handed a probelem and have to deal with it. And I appreciate all the people who really try to help solve it. and to the rest I enjoy learning new words 🙂
Excuse me, but I was trying to help solve the problem; if you could change the schema (and we have no way of knowing until you say otherwise), then I believe you agree with me that the best solution includes normalizing. And I did give you an artificially normalized solution in case you have no control over the schema.
--Jonathan
--Jonathan -
I know you were trying to help Jonathan. And I really do appreciate it!!!!! HAPPY HOLIDAYS
)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply