urgent help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

urgent help with query

Author

Topic

iissa69
Starting Member

5 Posts

Posted - 2005-01-18 : 09:24:38

I am struggling with a query and need urgent help.

I have a table with fields

canid
skillLevel
percent
date

and example of the results wud be

canid skillLevel Percent Date
704 1 20 n/a
705 2 10 n/a
775 1 50 n/a

what i want to do is produce a query that would return a result from the following search critera

return all results that match

skilllevel 1 and 20 percent AND skillLevel 2 and 10 percent

I tried this below but obviously im doing something wrong

select * from simulatedHistory where
((skilllevel = 1 and percent = 20) And (skilllevel = 2 and percent = 10))

I need the query returning

canid skillLevel Percent Date
704 1 20 n/a
705 2 10 n/a

any suggestions??? any help wud be much appreciated

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-18 : 09:35:36

use union?
select t.* from
(
select * from simulatedHistory where
skilllevel = 1 and percent = 20
union all
select * from simulatedHistory where
skilllevel = 2 and percent = 10
) t

Go with the flow & have fun! Else fight the flow

iissa69
Starting Member

5 Posts

Posted - 2005-01-18 : 09:49:21

that kinda worked, but if it matches one of the two select statements, it returns a row.... but i want it to only return when it matches both of the statements!

:D

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-18 : 09:50:59

then i believe you just need to change the and to or:
select * from simulatedHistory where
((skilllevel = 1 and percent = 20) OR (skilllevel = 2 and percent = 10))

Go with the flow & have fun! Else fight the flow

iissa69
Starting Member

5 Posts

Posted - 2005-01-18 : 09:55:19

i tried that before posting the topic, but this kinda does a similar job to the union, if it matches one lot of statement, then it will return the result, and if it matches the other statment it will return the other lot of results..... i need it to match both sets of statements for a result to return!

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-01-18 : 10:14:59

quote:
Originally posted by iissa69...
select * from simulatedHistory where
((skilllevel = 1 and percent = 20) And (skilllevel = 2 and percent = 10))
...

this should work just fine

select * from simulatedHistory where
(skilllevel = 1 and percent = 20) or(skilllevel = 2 and percent = 10)

can you post the ddl of the table?

.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.
A candle loses nothing by lighting another candle

iissa69
Starting Member

5 Posts

Posted - 2005-01-18 : 10:36:52

this is the same reply as spirit1, but i need it to match that two lot of statements, not just one or the other!!! it has to match both statments, and then return the result!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-19 : 03:07:07

will this work for you then?



declare @simulatedHistory table(canid int, skillLevel int, [Percent] int)
insert into @simulatedHistory
select 704, 1, 20 union all
select 705, 2, 10 union all
select 775, 1, 50

select 	* 
from 	@simulatedHistory 
where 	skilllevel in (1, 2)
	and [percent] in (10, 20)

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-19 : 08:51:33

this should work for you -- you need to ensure that exactly 2 rows are returned for each CanID:


select canID       -- not * !
from simulatedHistory 
where
(skilllevel = 1 and percent = 20) or (skilllevel = 2 and percent = 10)
group by CanID
having count(*)=2

- Jeff

iissa69
Starting Member

5 Posts

Posted - 2005-01-21 : 04:11:41

works a treat! thanks for all your help people!

Much Appreciated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources