Query to get rows which match with all given values

Transact-SQL

Jerome_H


Hi all,

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

Top

Re: Query to get rows which match with all given values

rusag2


Is is possible that a user may have scores that repeat (for exmaple, 2, 2, 4, 6, 6)






Top

Re: Query to get rows which match with all given values

rusag2

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)

)






Top

Re: Query to get rows which match with all given values

Jerome_H

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.


Top

Re: Query to get rows which match with all given values

rusag2

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

)





Top

Re: Query to get rows which match with all given values

Kiran.Y (GGK Tech)

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





Top

Re: Query to get rows which match with all given values

hunchback

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


Top

Re: Query to get rows which match with all given values

Jerome_H

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


Top