I would like have your help about a query.
In fact, I have a query to retrieve the rows for specific ID.
Like that:
SELECT *
FROM TblUser u
WHERE EXISTS
(
SELECT *
FROM TblScore s
WHERE s.FKIDUser = PKIDUser
)
With this query, I retrieve all users for which ones there are some scores.
Now, I need to get only users with specific score.
In the table TblScore, there is a column ScoreValue.
This column contains a value between 1 and 15
I would like to retrieve the users having score equal to 2,4 and 6
I could add a where clause like that: "and scorevalue in (2,4,6)"
But I want only users having these and only these scores, not less, not more.
So if an user has the following scores: 2,4,6,8, I don't want to retrieve it
If an user has the following scores: 2;4, I don't want to retrieve it.
If an user has the following scores: 2,4,6, I want it.
Someboy would have an idea at my problem ?
Thanks in advance
Jerome
Is is possible that a user may have scores that repeat? (for exmaple, 2, 2, 4, 6, 6) ?
|||Something like this might work in 2005, but not in 2000
Code Snippet
select * from tblUser U
where PKIDUser IN
(
(
select FKIDUser from tblscore where ScoreValue = 2
intersect
select FKIDUser from tblscore where ScoreValue = 4
intersect
select FKIDUser from tblscore where ScoreValue = 6
)
except
select FKIDUser from tblScore where ScoreValue not in (2,4,6)
)
|||No, it's not possible.And to complicate the problem, this query will be created in a stored procedure.
The specific score to search will be passed in argument to the sp.
For that, no problem, I can do it.
And I will insert these score into a temporary table (data type).
I tried to use the " = all " but I'm not sure it's the right solution.
|||
In that case, you can COUNT the records....if the total count = 3 and you've used IN (2,4,6) and these numbers cannot repeat...well, then you have your list:
Code Snippet
select * from tblUser
where PKIDUser in (
select FKIDUser
from TBLSCore
where ScoreValue in (2,4,6)
group by FKIDUser
having count(*) = 3
)
|||Hi,
May be you can try something like this:
SELECT
U.*
FROM tblUsers As U
JOIN (
SELECT FKIDUser FROM tblScores WHERE Score = 2
UNION
SELECT FKIDUser FROM tblScores WHERE Score = 4
UNION
SELECT FKIDUser FROM tblScores WHERE Score = 6
) AS T
ON U.UserID = T.FKIDUser
Thanks & Regards,
Kiran.Y
|||
This operation is known as "relational division".
declare @.t table(scorevalue int not null unique)
insert into @.t values(2)
insert into @.t values(4)
insert into @.t values(6)
select
u.userid
from
tblusers as u
inner join
tblscore as s
on s.fkuserid = u.pkuserid
inner join
@.t t
s.scorevalue = t.scorevalue
group by
u.userid
having
count(distinct s.score) = (select count(*) from @.t)
go
I am editing my post, because I realized, while running, that you want to kick it up another notch. So, if we add the following expression to the "having"clause, then we could get the expected result.
and (select coun(distinct s2.scorevalue) from tblscore as s2 where s2.fkuserid = u.pkuserid) = (select count(*) from @.t)
Also, we can give it a try to the use of "for xml" black box, to calculate concatenate aggregation (I think that my English here is far from good).
;with agg
as
(
select
userid,
stuff(
(
select ',' + ltrim(s.scorevalue)
from tblscore as s
where s.fkuserid = u.pkuserid
order by s.scorevalue
for xml path('')
), 1, 1, '') as conc_agg
from
tbluser as u
)
select
*
from
agg
where
conc_agg = '2,4,6';
AMB
|||Hi everybody,Thank you for your replies and sorry for my late answer but I was on vacation ^^
Finally, I used this solution:
DECLARE @.ScoreWanted TABLE (Score INT) -- 'ScoreWanted' score list
INSERT INTO @.ScoreWanted (Score) SELECT 3
INSERT INTO @.ScoreWanted (Score) SELECT 4
INSERT INTO @.ScoreWanted (Score) SELECT 6
SELECT *
FROM (
SELECT *
FROM users p
WHERE p.zone = @.p_Zone
AND p.region = @.p_Region
AND p.zipcode = @.p_ZipCode
AND p.valid = 1
AND p.called > 0
AND NOT EXISTS
(
SELECT 'x'
FROM score s
WHERE s.fk_user = p.id
AND date > @.p_PivotDate
) -- We don't keep users having a score after the pivot date
) a
WHERE a.id IN
(
SELECT s.fk_user
FROM score s
WHERE date <= @.p_PivotDate
AND s.score IN (SELECT * FROM @.ScoreWanted)
GROUP BY s.fk_user
HAVING COUNT(*) = (SELECT COUNT(*) FROM @.scorewanted)
) -- We keep only prospects having exactly the same scores that the scores coming from the
I perform a subquery for second part of my query for performance reason.
Like that I decrease the number of rows for which I need to do the second join.
Compared with my first explanation, there is here an other constraint: the pivot date for the score.
I want only users having all specified score before the pivot date and I don't want users with scores before the pivot date.
It's for that I have two separtes "where" clauses.
But I think I need to add the "distinct" word like Hunchback said.
For the xml code, Like I use Sql 2000, I think it's not supported ?
If you have anothers remarks, I'm listening you.
Thanks you.
Jerome
No comments:
Post a Comment