April 3, 2008 at 3:21 pm
I have a table with a column alpha_score_1 in this column will only be these values f1, f2, f3, p1, p2, p3. I would like to find the people who have a f1, f2, or f3, in that column and NO p values. This is what i have, but it doesnt work, can can i solve this?
select *
from testscores as ts1
where alpha_score_1 like 'f%'
and not exists (select * from testscores where alpha_score_1 like 'p%')
April 3, 2008 at 3:25 pm
I tried this as well
select *
from testscores as ts1
where alpha_score_1 like 'f%'
and alpha_score_1 not like 'p%'
and im not getting the right people, its just not showing the record containing the p value. For example i have one person who has 3 records in this table, f1, f2, and p1. I would NOT want this person on my report because they have a p1 value in that column, even though, they also have f1 and f2. Does that make sense? Please help as this is an urgent issue. Thanks!
April 3, 2008 at 3:29 pm
Sample Data, Actual Structure please.
Are the f1,f2,f3,p1,etc values all in the field on a single record, or does each f1,f2,p1,p2 have it's own record?
April 3, 2008 at 3:31 pm
Each f1, f2 ect has its own record in the table.
April 3, 2008 at 3:40 pm
Assuming that testscores has an id column
and you are looking for persons that have only f1, f2 and f3 and no p1, p2,p3
How about:
select *
from testscores as ts1
where alpha_score_1 like 'f%'
and id not in (select id from testscores where alpha_score_1 like 'p%')
April 3, 2008 at 3:43 pm
The problem then is that your query is filtering record by record. Just because the table has multiple records for the same user and some have the p1,p2 doesn't mean the ones with f1,f2 are going to care.
Try instead something like
select *
from testscores as ts1
where alpha_score_1 like 'f%'
and testtaker not in (select testtaker from testscores where alpha_score_1 like 'p%')
April 3, 2008 at 3:57 pm
In your above example, testtaker is my id column?
April 3, 2008 at 4:00 pm
Yes... I took a wildswing as to what the field may or may not be called.
It's also why I asked for the structure. 🙂
April 3, 2008 at 4:01 pm
Can you explain why this doesnt work?
select *
from testscores as ts1
where alpha_score_1 like 'f%'
and not exists (select * from testscores where alpha_score_1 like 'p%')
April 3, 2008 at 4:09 pm
Because you used EXISTS/NOT EXISTS instead of IN.
Books Online
When a subquery is introduced with the keyword EXISTS, it functions as an existence test. The WHERE clause of the outer query tests for the existence of rows returned by the subquery. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.
April 3, 2008 at 4:13 pm
The people code id identifies the person, but the table has a composite primary key with these columns
people_code_id
test_id
test_type
test_date
does this change anything?
April 3, 2008 at 4:14 pm
To use EXISTS you'd need to get craftier
select *
from testscores as ts1
where alpha_score_1 like 'f%'
and not exists (select * from testscores where alpha_score_1 like 'p%')
Becomes
select *
from testscores as ts1
where alpha_score_1 like 'f%'
and NOT EXISTS (
select * from testscores ts2
where ts1.id = ts2.id and
ts2.alpha_score_1 like 'p%'
)
The code above might need a little tweaking... I usually write using the IN/NOT IN operators for this sort of test.
April 3, 2008 at 4:15 pm
mtassin (4/3/2008)
Because you used EXISTS/NOT EXISTS instead of IN.Books Online
When a subquery is introduced with the keyword EXISTS, it functions as an existence test. The WHERE clause of the outer query tests for the existence of rows returned by the subquery. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.
That makes perfect sense. I REALLY appreciate your help.
April 3, 2008 at 4:16 pm
craig (4/3/2008)
The people code id identifies the person, but the table has a composite primary key with these columnspeople_code_id
test_id
test_type
test_date
does this change anything?
Nope, as long as a single field identifies a person, you can still do it with in. If you needed to handle multiple columns you'd either use the EXISTS/NOT EXISTS, or create a join to test for it
April 3, 2008 at 4:17 pm
Thanks again, you saved me a bid headache. I just got stumped for some reason, but lesson learned.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply