Showing posts with label fact. Show all posts
Showing posts with label fact. Show all posts

Monday, March 12, 2012

Query to get rows which match with all given values

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


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

Query to get rows which match with all given values

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


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

Friday, March 9, 2012

Query to determine if something exists versus just trying to create it...

I have run into two situations in the recent past that both have the
same thing in common. I have to preface this with the fact that I am
running the following queries in a C#/.Net environment using SQL Server
Express...
The question is, and it may be silly, but, should one query for the
existance of a row in the DB before attempting to create it?
For example, I could write a query that says "select * from table where
id = 1". Then if the resulting dataset has one or 0 rows, I could
determine if I need to write a Update or Insert query to put in the
row. However, I am assuming that the rows will 99% of the time already
exist, but I need to update all of the fields in the table with
potentially new values. So the question is, in this case, it would be
silly to attempt to determine if the row is there, then to determine if
it should be updated or not. Basically, I am executing the update
statement first, and if it returns 0 (meaning that the row didn't
exist), then I translate the statement from an update to an insert...
I am also doing something similar with create/alter statements, where I
could test if the table/field exists in the DB before attempting to
create or alter the table, but why bother if I can execute the one
statement and then know if the table or field already existed based off
of if the statement executed correctly or not... ?
So, is there a faster way to say "insert OR update this data", in one
statement, or should I just continue executing one, then the other if
the first one fails?
AB> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values.
If the usual case is that the row will exist, you might consider trying the
update first and then proceeding with the insert only if no rows were
updated. Something like:
CREATE PROC usp_SaveMyTable
@.MyTableId int,
@.SomeColumn int
AS
SET NOCOUNT, XACT_ABORT ON
DECLARE @.Error int, @.RowCount int
BEGIN TRAN
UPDATE dbo.MyTable
SET SomeColumn = @.SomeColumn
WHERE MyTableId = @.MyTableId
SELECT @.Error = @.@.ERROR, @.RowCount = @.@.ROWCOUNT
IF @.RowCount > 0 OR @.Error <> 0
BEGIN
GOTO Done
END
INSERT INTO dbo.MyTable(MyTableId, SomeColumn)
SELECT
@.MyTableId, @.SomeColumn
WHERE NOT EXISTS
(
SELECT *
FROM dbo.MyTable WITH (HOLDLOCK)
WHERE MyTableId = @.MyTableId
)
SELECT @.Error = @.@.ERROR
Done:
IF @.Error = 0
BEGIN
COMMIT
END
ELSE
BEGIN
ROLLBACK
END
GO
> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
Personally, I prefer to avoid raising errors for expected conditions. If
you get error messages even when the script runs successfully, it's hard to
tell a real problem with all the noise.
IF OBJECT_ID('dbo.MyTable') IS NULL
BEGIN
CREATE TABLE dbo.MyTable
(
MyTableId int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
SomeColumn int NOT NULL
)
END
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Beavis" <multiformity@.gmail.com> wrote in message
news:1156385524.486272.138630@.p79g2000cwp.googlegroups.com...
>I have run into two situations in the recent past that both have the
> same thing in common. I have to preface this with the fact that I am
> running the following queries in a C#/.Net environment using SQL Server
> Express...
> The question is, and it may be silly, but, should one query for the
> existance of a row in the DB before attempting to create it?
> For example, I could write a query that says "select * from table where
> id = 1". Then if the resulting dataset has one or 0 rows, I could
> determine if I need to write a Update or Insert query to put in the
> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values. So the question is, in this case, it would be
> silly to attempt to determine if the row is there, then to determine if
> it should be updated or not. Basically, I am executing the update
> statement first, and if it returns 0 (meaning that the row didn't
> exist), then I translate the statement from an update to an insert...
> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
> So, is there a faster way to say "insert OR update this data", in one
> statement, or should I just continue executing one, then the other if
> the first one fails?
> AB
>

Query to determine if something exists versus just trying to create it...

I have run into two situations in the recent past that both have the
same thing in common. I have to preface this with the fact that I am
running the following queries in a C#/.Net environment using SQL Server
Express...
The question is, and it may be silly, but, should one query for the
existance of a row in the DB before attempting to create it?
For example, I could write a query that says "select * from table where
id = 1". Then if the resulting dataset has one or 0 rows, I could
determine if I need to write a Update or Insert query to put in the
row. However, I am assuming that the rows will 99% of the time already
exist, but I need to update all of the fields in the table with
potentially new values. So the question is, in this case, it would be
silly to attempt to determine if the row is there, then to determine if
it should be updated or not. Basically, I am executing the update
statement first, and if it returns 0 (meaning that the row didn't
exist), then I translate the statement from an update to an insert...
I am also doing something similar with create/alter statements, where I
could test if the table/field exists in the DB before attempting to
create or alter the table, but why bother if I can execute the one
statement and then know if the table or field already existed based off
of if the statement executed correctly or not... ?
So, is there a faster way to say "insert OR update this data", in one
statement, or should I just continue executing one, then the other if
the first one fails?
AB> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values.
If the usual case is that the row will exist, you might consider trying the
update first and then proceeding with the insert only if no rows were
updated. Something like:
CREATE PROC usp_SaveMyTable
@.MyTableId int,
@.SomeColumn int
AS
SET NOCOUNT, XACT_ABORT ON
DECLARE @.Error int, @.RowCount int
BEGIN TRAN
UPDATE dbo.MyTable
SET SomeColumn = @.SomeColumn
WHERE MyTableId = @.MyTableId
SELECT @.Error = @.@.ERROR, @.RowCount = @.@.ROWCOUNT
IF @.RowCount > 0 OR @.Error <> 0
BEGIN
GOTO Done
END
INSERT INTO dbo.MyTable(MyTableId, SomeColumn)
SELECT
@.MyTableId, @.SomeColumn
WHERE NOT EXISTS
(
SELECT *
FROM dbo.MyTable WITH (HOLDLOCK)
WHERE MyTableId = @.MyTableId
)
SELECT @.Error = @.@.ERROR
Done:
IF @.Error = 0
BEGIN
COMMIT
END
ELSE
BEGIN
ROLLBACK
END
GO

> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
Personally, I prefer to avoid raising errors for expected conditions. If
you get error messages even when the script runs successfully, it's hard to
tell a real problem with all the noise.
IF OBJECT_ID('dbo.MyTable') IS NULL
BEGIN
CREATE TABLE dbo.MyTable
(
MyTableId int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
SomeColumn int NOT NULL
)
END
Hope this helps.
Dan Guzman
SQL Server MVP
"Beavis" <multiformity@.gmail.com> wrote in message
news:1156385524.486272.138630@.p79g2000cwp.googlegroups.com...
>I have run into two situations in the recent past that both have the
> same thing in common. I have to preface this with the fact that I am
> running the following queries in a C#/.Net environment using SQL Server
> Express...
> The question is, and it may be silly, but, should one query for the
> existance of a row in the DB before attempting to create it?
> For example, I could write a query that says "select * from table where
> id = 1". Then if the resulting dataset has one or 0 rows, I could
> determine if I need to write a Update or Insert query to put in the
> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values. So the question is, in this case, it would be
> silly to attempt to determine if the row is there, then to determine if
> it should be updated or not. Basically, I am executing the update
> statement first, and if it returns 0 (meaning that the row didn't
> exist), then I translate the statement from an update to an insert...
> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
> So, is there a faster way to say "insert OR update this data", in one
> statement, or should I just continue executing one, then the other if
> the first one fails?
> AB
>