SQL server connection: select

Showing posts with label select. Show all posts

Tuesday, March 20, 2012

another way to write this query?

Hi
I have the following query
select studentid from students where studentid not in (select studentid from
coursestudent)
is there some way I could write this with using the 'not in'?
student is a table of students. coursestudent is a join table of courses to
students. I'm trying
to find those students who are not registered for a course. While the
above query works,
I'd like to find a more efficient query.
create table student
(studentid int identity primary key,
name varchar(50),
address1varchar(50),
address2 varchar(50),
city varchar(50),
state varchar(4),
zip varchar(11))
create table coursestudent
(courseid int
studentid int)
notes:
- non clustered index on courseid and non clustered index on studentid exist
- there is a foreign key relationship from coursestudent's studentid to
student studentid
try:
select a.studentid from students as a
left join coursestudent as b on a.studentid = b.studentid
where b.courseid is null
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
|||To determine which is most efficient, you need to look at the execution plans, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
|||Arnie
Please double-check - this shouldn't work
There should be left join
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
To determine which is most efficient, you need to look at the execution
plans, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
|||Why do you think the solution you have is not efficient?
In the pubs database, these two queries have an almost identical plan, but
the wone with the NOT IN is slightly cheaper:
use pubs
select pub_name from publishers
where pub_id not in (select pub_id from titles)
select pub_name
from publishers p left join titles t
on p.pub_id = t.pub_id
where t.pub_id is null
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
|||> is there some way I could write this with using the 'not in'?
Another method is with NOT EXISTS. NOT IN and NOT EXISTS will both yield
identical execution plans (and performance) as long as the queries are
semantically the same. The SQL language is descriptive rather than
procedural so the optimizer will try to generate the most efficient plan
possible based on your query statement. As long as the expressions are
sargable, most performance tuning is in making sure you have appropriate
indexes.
However, note that the coursestudent.studentid column allows null so the
queries are not the same semantically. With not NOT IN query, no rows will
be returned if *any* null studentid exists in the coursestudent table. The
NOT EXIST query will return the stundents you expect.
If the coursestudent studentid column were changed to allow nulls, then both
NOT IN and NOT EXISTS queries will be semantically identical.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
|||You are absolutely correct. I meant to include the LEFT on the join.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mikhail Berlyant" <remove-this-berlyant@.yahoo-inc.com> wrote in message
news:OzbZqbQ4GHA.3592@.TK2MSFTNGP05.phx.gbl...
> Arnie
> Please double-check - this shouldn't work
> There should be left join
> Mikhail Berlyant
> Eng.Manager
> Yahoo! Music
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
> To determine which is most efficient, you need to look at the execution
> plans, examine the I/O, and execution times.
> Here is an alternative:
> SELECT StudentID
> FROM Students s
> JOIN CourseStudent c
> ON s.StudentID = c.StudentID
> WHERE c.StudentID IS NULL
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> from
> to
> exist
>
|||thank you everyone
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>

another way to write this query?

To determine which is most efficient, =you need to look at the execution plans, examine the I/O, and execution =times.
Here is an alternative:
SELECT StudentID =FROM Students s JOIN CourseStudent c ON s.StudentID =3D =c.StudentIDWHERE c.StudentID IS NULL
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"Dodo Lurker" =wrote in message news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...> =Hi> > I have the following query> > select studentid =from students where studentid not in (select studentid from> coursestudent)> > is there some way I could write this =with using the 'not in'?> > student is a table of students. coursestudent is a join table of courses to> students. I'm trying> to find those students who are not registered for a course. While the> above query works,> I'd =like to find a more efficient query.> > create table =student> (studentid int identity primary key,> name varchar(50),> address1varchar(50),> address2 varchar(50),> city varchar(50),> state varchar(4),> zip varchar(11))> => create table coursestudent> (courseid int> studentid int)> > notes:> - non clustered index on courseid =and non clustered index on studentid exist> - there is a foreign key =relationship from coursestudent's studentid to> student studentid> => > >

--=_NextPart_000_0247_01C6E0CB.6186E090--|||Arnie
Please double-check - this shouldn't work
There should be left join
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
To determine which is most efficient, you need to look at the execution
plans, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||Why do you think the solution you have is not efficient?
In the pubs database, these two queries have an almost identical plan, but
the wone with the NOT IN is slightly cheaper:
use pubs
select pub_name from publishers
where pub_id not in (select pub_id from titles)
select pub_name
from publishers p left join titles t
on p.pub_id = t.pub_id
where t.pub_id is null
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||> is there some way I could write this with using the 'not in'?
Another method is with NOT EXISTS. NOT IN and NOT EXISTS will both yield
identical execution plans (and performance) as long as the queries are
semantically the same. The SQL language is descriptive rather than
procedural so the optimizer will try to generate the most efficient plan
possible based on your query statement. As long as the expressions are
sargable, most performance tuning is in making sure you have appropriate
indexes.
However, note that the coursestudent.studentid column allows null so the
queries are not the same semantically. With not NOT IN query, no rows will
be returned if *any* null studentid exists in the coursestudent table. The
NOT EXIST query will return the stundents you expect.
If the coursestudent studentid column were changed to allow nulls, then both
NOT IN and NOT EXISTS queries will be semantically identical.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||You are absolutely correct. I meant to include the LEFT on the join.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mikhail Berlyant" <remove-this-berlyant@.yahoo-inc.com> wrote in message
news:OzbZqbQ4GHA.3592@.TK2MSFTNGP05.phx.gbl...
> Arnie
> Please double-check - this shouldn't work
> There should be left join
> Mikhail Berlyant
> Eng.Manager
> Yahoo! Music
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
> To determine which is most efficient, you need to look at the execution
> plans, examine the I/O, and execution times.
> Here is an alternative:
> SELECT StudentID
> FROM Students s
> JOIN CourseStudent c
> ON s.StudentID = c.StudentID
> WHERE c.StudentID IS NULL
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
>> Hi
>> I have the following query
>> select studentid from students where studentid not in (select studentid
> from
>> coursestudent)
>> is there some way I could write this with using the 'not in'?
>> student is a table of students. coursestudent is a join table of courses
> to
>> students. I'm trying
>> to find those students who are not registered for a course. While the
>> above query works,
>> I'd like to find a more efficient query.
>> create table student
>> (studentid int identity primary key,
>> name varchar(50),
>> address1varchar(50),
>> address2 varchar(50),
>> city varchar(50),
>> state varchar(4),
>> zip varchar(11))
>> create table coursestudent
>> (courseid int
>> studentid int)
>> notes:
>> - non clustered index on courseid and non clustered index on studentid
> exist
>> - there is a foreign key relationship from coursestudent's studentid to
>> student studentid
>>
>>
>|||thank you everyone
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>

another way to write this query?

Hi
I have the following query
select studentid from students where studentid not in (select studentid from
coursestudent)
is there some way I could write this with using the 'not in'?
student is a table of students. coursestudent is a join table of courses to
students. I'm trying
to find those students who are not registered for a course. While the
above query works,
I'd like to find a more efficient query.
create table student
(studentid int identity primary key,
name varchar(50),
address1varchar(50),
address2 varchar(50),
city varchar(50),
state varchar(4),
zip varchar(11))
create table coursestudent
(courseid int
studentid int)
notes:
- non clustered index on courseid and non clustered index on studentid exist
- there is a foreign key relationship from coursestudent's studentid to
student studentidtry:
select a.studentid from students as a
left join coursestudent as b on a.studentid = b.studentid
where b.courseid is null
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||To determine which is most efficient, you need to look at the execution plan
s, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.co
mcast.com...
> Hi
>
> I have the following query
>
> select studentid from students where studentid not in (select studentid fr
om
> coursestudent)
>
> is there some way I could write this with using the 'not in'?
>
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
>
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
>
> create table coursestudent
> (courseid int
> studentid int)
>
> notes:
> - non clustered index on courseid and non clustered index on studentid exi
st
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
>
>|||Arnie
Please double-check - this shouldn't work
There should be left join
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
To determine which is most efficient, you need to look at the execution
plans, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||Why do you think the solution you have is not efficient?
In the pubs database, these two queries have an almost identical plan, but
the wone with the NOT IN is slightly cheaper:
use pubs
select pub_name from publishers
where pub_id not in (select pub_id from titles)
select pub_name
from publishers p left join titles t
on p.pub_id = t.pub_id
where t.pub_id is null
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||> is there some way I could write this with using the 'not in'?
Another method is with NOT EXISTS. NOT IN and NOT EXISTS will both yield
identical execution plans (and performance) as long as the queries are
semantically the same. The SQL language is descriptive rather than
procedural so the optimizer will try to generate the most efficient plan
possible based on your query statement. As long as the expressions are
sargable, most performance tuning is in making sure you have appropriate
indexes.
However, note that the coursestudent.studentid column allows null so the
queries are not the same semantically. With not NOT IN query, no rows will
be returned if *any* null studentid exists in the coursestudent table. The
NOT EXIST query will return the stundents you expect.
If the coursestudent studentid column were changed to allow nulls, then both
NOT IN and NOT EXISTS queries will be semantically identical.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||You are absolutely correct. I meant to include the LEFT on the join.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mikhail Berlyant" <remove-this-berlyant@.yahoo-inc.com> wrote in message
news:OzbZqbQ4GHA.3592@.TK2MSFTNGP05.phx.gbl...
> Arnie
> Please double-check - this shouldn't work
> There should be left join
> Mikhail Berlyant
> Eng.Manager
> Yahoo! Music
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
> To determine which is most efficient, you need to look at the execution
> plans, examine the I/O, and execution times.
> Here is an alternative:
> SELECT StudentID
> FROM Students s
> JOIN CourseStudent c
> ON s.StudentID = c.StudentID
> WHERE c.StudentID IS NULL
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> from
> to
> exist
>|||thank you everyone
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>

Monday, March 19, 2012

Another SelectCommand Distinct problem

why i type the select command like below the disctinct doesnt work? the query stil show all the Category i hav so how do i fix it??

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT DISTINCT Category, ID FROM Notes WHERE (UserName LIKE '%' + @.UserName + '%') ORDER BY ID DESC">

<asp:SessionParameterName="UserName"SessionField="UserName"Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

You may want to try

"SELECT DISTINCT Category FROM Notes WHERE (UserName LIKE '%' + @.UserName + '%') ORDER BY ID DESC"

The SELECT shown, viz., "Category, ID" brings all the distinct combinations of Category AND ID

Fouwaaz

|||

it work using singel feild.lets say if i select category, Title and weblink but i oni wan DISTINCT Category how do i change the code??

SELECT DISTINCT Category, Title,Weblink FROM Bookmarks WHERE (UserName = @.username) AND (Category = 'Top1' OR Category = 'Top2' OR Category = 'Top3' OR Category = 'Top4' OR Category = 'Top5')

now the distinct is handle 3 feild how to i make the distinct handle 1 feild?? bcoz i was trying to the output like this:

Original Data

Category Title Weblink
Top1 Top1Title www.top1title.com

Top1 Top2Title www.top2title.com

Top2 Top3Title www.top3title.com

Top2 Top4Title www.top4title.com

Top3 Top5Title www.top5title.com

Top3 Top6Title www.top6title.com

Output is the title become weblink...

|||

Sorry, it looks like I did not understand your question. This is the output you have shown

Category Title Weblink
Top1 Top1Title www.top1title.com

Top1 Top2Title www.top2title.com

Top2 Top3Title www.top3title.com

Top2 Top4Title www.top4title.com

Top3 Top5Title www.top5title.com

Top3 Top6Title www.top6title.com

Now, could you show the output that you would like to get?

Thanks

Fouwaaz

Sunday, March 11, 2012

Another question (IN keyword)

Is possible in SQLSERVER to use a syntax like this?

select x,y from table_1 where (x,y) not in (select h,k from table_2)

I've tried, but it doen't work.

Do you know any workaround?

Thank you

Fede"Federica T" <fedina_chicca@.N_O_Spam_libero.it> wrote in message
news:cjc1bn$nsk$1@.atlantis.cu.mi.it...
> Is possible in SQLSERVER to use a syntax like this?
> select x,y from table_1 where (x,y) not in (select h,k from table_2)
> I've tried, but it doen't work.
> Do you know any workaround?
> Thank you
> Fede

No, that's not supported in MSSQL - you can use a correlated subquery
instead:

select x, y
from table_1 t1
where not exists (
select *
from table_2 t2
where t1.x = t2.h
and t1.y = t2.k)

Unfortunately, Microsoft doesn't seem to have a DB2 to MSSQL technical
migration guide (they do exist for other database platforms), but you might
still find some useful stuff here:

http://www.microsoft.com/sql/evalua...ibm/default.asp

Simon|||> No, that's not supported in MSSQL - you can use a correlated subquery
> instead:
> select x, y
> from table_1 t1
> where not exists (
> select *
> from table_2 t2
> where t1.x = t2.h
> and t1.y = t2.k)
> Unfortunately, Microsoft doesn't seem to have a DB2 to MSSQL technical
> migration guide (they do exist for other database platforms), but you
might
> still find some useful stuff here:
> http://www.microsoft.com/sql/evalua...ibm/default.asp
> Simon

Thank you very much!

Fede|||"Federica T" <fedina_chicca@.N_O_Spam_libero.it> wrote in message news:<cjdp11$nm6$1@.atlantis.cu.mi.it>...
> > No, that's not supported in MSSQL - you can use a correlated subquery
> > instead:
> > select x, y
> > from table_1 t1
> > where not exists (
> > select *
> > from table_2 t2
> > where t1.x = t2.h
> > and t1.y = t2.k)
> > Unfortunately, Microsoft doesn't seem to have a DB2 to MSSQL technical
> > migration guide (they do exist for other database platforms), but you
> might
> > still find some useful stuff here:
> > http://www.microsoft.com/sql/evalua...ibm/default.asp
> > Simon
> Thank you very much!
> Fede

Or, somewhat equivalently, you could do:

SELECT t1.x,t1.y FROM Table_1 t1 LEFT JOIN Table_2 t2 ON t1.x = t2.h
and t1.y = t2.k WHERE t2.h IS null

Sorry, just have an intense dislike of "not in" and "not exists". This
second form may (or may not, YMMV) perform better

another question

Hi guys,
please create a table in tempdb running following
USE tempdb
CREATE TABLE delete_me (c1 int, c2 int )
INSERT delete_me (c1, c2)
SELECT 1, 1 UNION SELECT 2, 2 UNION SELECT 3, 3 UNION SELECT 4, 4
Then running the script below you can get (I do) the error message :
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'c3'.
But sometimes it works. The workaround seems to be to wrap UPDATE statement
up into EXEC, - it works always. Why is that?
BEGIN TRAN
ALTER TABLE delete_me
ADD c3 int
-- EXEC ('UPDATE delete_me SET c3 = 0')
UPDATE delete_me SET c3 = 0
ALTER TABLE delete_me
ALTER COLUMN c3 int NOT NULL
ROLLBACK TRAN
Thanks
AlexThats quite a normal behaviour. Object resolution takes place if the
object is already know so, this will fail due to the non existing
column. Look for
http://msdn.microsoft.com/library/d...>
_07_5wa6.asp
"Note Deferred Name Resolution can only be used when you reference
nonexistent table objects. All other objects must exist at the time the
stored procedure is created. For example, when you reference an
existing table in a stored procedure you cannot list nonexistent
columns for that table."
HTH, Jens Suessmeyer.|||AlexM
What is your SQL Server version?
It worked fine on my workstation (SS2000,SP3,Personal Edition)
"AlexM" <alex_remove_this_mak@.telus.net> wrote in message
news:CHYDf.157789$AP5.28253@.edtnps84...
> Hi guys,
> please create a table in tempdb running following
> USE tempdb
> CREATE TABLE delete_me (c1 int, c2 int )
> INSERT delete_me (c1, c2)
> SELECT 1, 1 UNION SELECT 2, 2 UNION SELECT 3, 3 UNION SELECT 4, 4
> Then running the script below you can get (I do) the error message :
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'c3'.
> But sometimes it works. The workaround seems to be to wrap UPDATE
> statement up into EXEC, - it works always. Why is that?
>
> BEGIN TRAN
> ALTER TABLE delete_me
> ADD c3 int
> -- EXEC ('UPDATE delete_me SET c3 = 0')
> UPDATE delete_me SET c3 = 0
> ALTER TABLE delete_me
> ALTER COLUMN c3 int NOT NULL
>
> ROLLBACK TRAN
>
> Thanks
> Alex
>|||Thanks Jens, that note apparently slipped my mind ;-)
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1138778592.061349.312360@.o13g2000cwo.googlegroups.com...
> Thats quite a normal behaviour. Object resolution takes place if the
> object is already know so, this will fail due to the non existing
> column. Look for
> http://msdn.microsoft.com/library/d...
es_07_5wa6.asp
> "Note Deferred Name Resolution can only be used when you reference
> nonexistent table objects. All other objects must exist at the time the
> stored procedure is created. For example, when you reference an
> existing table in a stored procedure you cannot list nonexistent
> columns for that table."
>
> HTH, Jens Suessmeyer.
>|||ss2000, enterprise & developer, sp4
Jens pointed to the note which explains clearly why it happens. What it
worries me though that this behaviour is not consistent. Most of the tine it
acts according to BOL and that particular note, but sometimes the resolution
stage comes through with flying colors when referencing a missing column for
existing table. But this is a bit different story...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eL4xwIwJGHA.3332@.TK2MSFTNGP11.phx.gbl...
> AlexM
> What is your SQL Server version?
> It worked fine on my workstation (SS2000,SP3,Personal Edition)
>
> "AlexM" <alex_remove_this_mak@.telus.net> wrote in message
> news:CHYDf.157789$AP5.28253@.edtnps84...
>

another query question

Hi,
How do you do this in Pubs?
Select the types of books that had an average price > $13.50
I can only go so far:
select type, avg(price) from titles group by typenospam
select type, avg(price) from titles
group by type
HAVING avg(price) >13
"nospam" <hello@.hotmail.com> wrote in message
news:TuOdnavPvLGCLciiRVn-hA@.giganews.com...
> Hi,
> How do you do this in Pubs?
> Select the types of books that had an average price > $13.50
>
> I can only go so far:
> select type, avg(price) from titles group by type
>

Another Query Question

Suppose you have a table in which one of the fields can have any
number of the same values. Is there a way to select on that field for
only those records where there is a single occurrence of that value in
the entire table ? I don't want any records returned by the query if
there is more than one occurrence, just if there's one. Thanks all.

Rick."Rick" <snarfie.mcdougal@.comcast.net> wrote in message
news:7b5ae645.0312110640.3eec56c4@.posting.google.c om...
> Suppose you have a table in which one of the fields can have any
> number of the same values. Is there a way to select on that field for
> only those records where there is a single occurrence of that value in
> the entire table ? I don't want any records returned by the query if
> there is more than one occurrence, just if there's one. Thanks all.
> Rick.

This is one way to do it, using the Northwind database - find any customers
who have only one row in the Orders table:

select * from
Orders t join
(
select CustomerID
from Orders
group by CustomerID
having count(*) = 1
) dt
on t.CustomerID = dt.CustomerID

Simon|||snarfie.mcdougal@.comcast.net (Rick) wrote in message news:<7b5ae645.0312110640.3eec56c4@.posting.google.com>...
> Suppose you have a table in which one of the fields can have any
> number of the same values. Is there a way to select on that field for
> only those records where there is a single occurrence of that value in
> the entire table ? I don't want any records returned by the query if
> there is more than one occurrence, just if there's one. Thanks all.
> Rick.
Hi Rick,

Are you talking about duplicate records? "Values" and "occurences"
are a little ambiguous. -- Louis

create table #T(x int)
insert into #T values(1)
insert into #T values(2)
insert into #T values(2)
insert into #T values(3)
insert into #T values(3)
insert into #T values(3)

select x
from #T
group by x
having count(*)=1

returns:
x
----
1|||snarfie.mcdougal@.comcast.net (Rick) wrote in message news:<7b5ae645.0312110640.3eec56c4@.posting.google.com>...
> Suppose you have a table in which one of the fields can have any
> number of the same values. Is there a way to select on that field for
> only those records where there is a single occurrence of that value in
> the entire table ? I don't want any records returned by the query if
> there is more than one occurrence, just if there's one. Thanks all.
> Rick.

To find single occurrances for a column...

select col1
count(*) as col_cnt
from table1
group by col1
having count(*) = 1;

so to return the rows with single occurances, join the above back to
the original table...

select t.*
from table 1 as t
(select col1
count(*) as col_cnt
from table1
group by col1
having count(*) = 1
) as s
where t.col1 = s.col1;

Christian.|||ok, forgive me cause I'm doing this strictly out of memory, but its
close...

Select col1, count(*)
from mytable
group by col1
having count(*) = 1

declare @.tResults TABLE (mycol int, rowcount int)
insert into @.tResults
Select col1, count(*)
from mytable
group by col1
having count(*) = 1

select mycol from @.tResults where rowcount = 1

"Rick" <snarfie.mcdougal@.comcast.net> wrote in message
news:7b5ae645.0312110640.3eec56c4@.posting.google.c om...
> Suppose you have a table in which one of the fields can have any
> number of the same values. Is there a way to select on that field for
> only those records where there is a single occurrence of that value in
> the entire table ? I don't want any records returned by the query if
> there is more than one occurrence, just if there's one. Thanks all.
> Rick.

Another Problem with variables

I have this...

declare
@.variable int

create table #tmp_datos
( variable int null )

insert into #tmp_datos
exec sp_prueba_era

select @.variable = variable
from
#tmp_datos

select @.variable

drop proc sp_prueba_era
go

create proc sp_prueba_era @.variable int output
as
declare
@.cmd varchar(250)

are there other way to do this??are you able to connect to your server using isql ?|||Yes, but I need to pass dinamically the name of the server, by this I'm using strings and exec...

But the original query it's this...

Select @.var = Select 1 from xx

I need to pass this to string...|||Originally posted by ramshree
are you able to connect to your server using isql ?

Not clear what you are trying to do.

but this works :

declare @.var char(100)
select @.var = 'select * from sysobjects'
exec (@.var)

-|||I'm trying to store a value in a variable into select statement, but my select statement it's a string that I'm going to execute with exec clause..., so I can't to do this...

declare
@.variable int,
@.cmd varchar(100)

select @.cmd = "select @.variable = 20"
exec (@.cmd)

because @.variable it's not a defined variable..., so how can I do for make a string like this...|||Maybe there is something, i haven't understood, but why is it so important, that the select is stored in the string?|||Hi,
Hope this is wat u want--
here i am assigning the count of the number of records to the variable @.varCountTable. but the whole string has to be executed in EXEC().
Personalize it to ur needs--
SET @.varSelectString = N'SELECT @.varCountTable = COUNT(*) FROM CPGSTAGEDB.DBO.' + @.varTableName
EXEC SP_EXECUTESQL @.varSelectString, N'@.varCountTable nvarchar(50) OUTPUT',@.varCountTable = @.varCountTable OUTPUT

Regards,
Ramya

Originally posted by ericka
I have this...

declare
@.variable int

create table #tmp_datos
( variable int null )

insert into #tmp_datos
exec sp_prueba_era

select @.variable = variable
from
#tmp_datos

select @.variable

drop proc sp_prueba_era
go

create proc sp_prueba_era @.variable int output
as
declare
@.cmd varchar(250)

are there other way to do this??

Another Newb SQL Question

me again. been staring at this and can't see what's wrong.
SELECT tblCustomers.companyName, tblOrders.orderID,
tblOrders.freightCharge, tblOrderDetails.unitPriceOnOrderDate
FROM tblCustomers
JOIN tblOrders
ON tblCustomers.customerID = tblOrders.orderID
JOIN tblOrderDetails
ON tblOrders.orderID = tblOrderDetails.orderID
WHERE EXISTS
(
SELECT tblOrders.orderID
FROM tblOrders
JOIN tblOrderDetails
ON tblOrders.orderID = tblOrderDetails.orderID
GROUP BY tblOrders.orderID, tblOrderDetails.orderID,
tblOrders.freightCharge
HAVING tblOrders.orderID = tblOrderDetails.orderID and
tblOrders.freightCharge > MIN(tblOrderDetails.unitPriceOnOrderDate)
)
idea is to show all orders whose freight charge (contained in
tblOrders) exceeds the unit price (contained in tblOrderDetails) of any
product in that order.
unfortunately results from my query contain many orders where the
freight charge is nowhere near as high as the prices of the products.
thxPlease post DDL and sample data.
ML|||<A_StClaire_@.hotmail.com> wrote in message
news:1130611630.835990.38500@.g49g2000cwa.googlegroups.com...
> me again. been staring at this and can't see what's wrong.
>
<snip>

> idea is to show all orders whose freight charge (contained in
> tblOrders) exceeds the unit price (contained in tblOrderDetails) of
any
> product in that order.
> unfortunately results from my query contain many orders where the
> freight charge is nowhere near as high as the prices of the
products.
> thx
>
A_StClaire_@.hotmail.com,
Original Query:
SELECT tblCustomers.companyName
,tblOrders.orderID
,tblOrders.freightCharge
,tblOrderDetails.unitPriceOnOrderDate
FROM tblCustomers
JOIN
tblOrders
ON tblCustomers.customerID = tblOrders.orderID
JOIN
tblOrderDetails
ON tblOrders.orderID = tblOrderDetails.orderID
WHERE EXISTS
(SELECT tblOrders.orderID
FROM tblOrders
JOIN
tblOrderDetails
ON tblOrders.orderID = tblOrderDetails.orderID
GROUP BY tblOrders.orderID
,tblOrderDetails.orderID
,tblOrders.freightCharge
HAVING tblOrders.orderID = tblOrderDetails.orderID
and tblOrders.freightCharge >
MIN(tblOrderDetails.unitPriceOnOrderDate)
)
The outer query has:
tblOrders
tblCustomers
tblOrderDetails
The subquery has:
tblOrders
tblOrderDetails
Note: In the outer query, tblOrder and tblCustomers are JOINed on
customerID = orderID. Is this correct (I can't imagine it would be)?
I changed it in example below in the hopes I was interpreting things
correctly.
--
In order for an EXISTS predicate to function properly (AFAIK, anyway),
the subquery must be correlated with the outer query.
I cannot spot the correlation in the above SQL. Without Table
Aliases, the query optimizer has no way of linking the subquery to the
outer query.
The following is the original Query now reformatted with Table Aliases
and a WHERE clause correlating the subquery and the outer query.
SELECT C1.companyName
,O1.orderID
,O1.freightCharge
,OD1.unitPriceOnOrderDate
FROM tblCustomers AS C1
INNER JOIN
tblOrders AS O1
ON C1.customerID = O1.customerID
INNER JOIN
tblOrderDetails AS OD1
ON O1.orderID = OD1.orderID
WHERE EXISTS
(SELECT O2.orderID
FROM tblOrders AS O2
INNER JOIN
tblOrderDetails AS OD2
ON O2.orderID = OD2.orderID
WHERE O1.orderID = O2.orderID
GROUP BY O2.orderID
,OD2.orderID
,O2.freightCharge
HAVING O2.orderID = OD2.orderID
and O2.freightCharge >
MIN(OD2.unitPriceOnOrderDate)
)
Note: Notice how the WHERE clause refers to a table in the outer query
by using the alias of the table in the outer query.
Note: I am making a fairly huge guess on the proper correlation in the
WHERE clause of the subquery. Pleaes take it for what it is meant to
be, a hint directing you toward a solution. IOW, this is definitely
not tested.
Sincerely,
Chris O.
PS Although meant for microsoft.pulic.sqlserver.programming, the
following link is still applicable for microsoft.pulic.access.queries:
http://www.aspfaq.com/etiquette.asp?id=5006, when it comes to
providing the information that will best enable others to answer your
question.|||<A_StClaire_@.hotmail.com> wrote in message
news:1130611630.835990.38500@.g49g2000cwa.googlegroups.com...
> me again. been staring at this and can't see what's wrong.
>
<snip>

> idea is to show all orders whose freight charge (contained in
> tblOrders) exceeds the unit price (contained in tblOrderDetails) of
any
> product in that order.
> unfortunately results from my query contain many orders where the
> freight charge is nowhere near as high as the prices of the
products.
> thx
>
A_StClaire_@.hotmail.com,
Original Query:
SELECT tblCustomers.companyName
,tblOrders.orderID
,tblOrders.freightCharge
,tblOrderDetails.unitPriceOnOrderDate
FROM tblCustomers
JOIN
tblOrders
ON tblCustomers.customerID = tblOrders.orderID
JOIN
tblOrderDetails
ON tblOrders.orderID = tblOrderDetails.orderID
WHERE EXISTS
(SELECT tblOrders.orderID
FROM tblOrders
JOIN
tblOrderDetails
ON tblOrders.orderID = tblOrderDetails.orderID
GROUP BY tblOrders.orderID
,tblOrderDetails.orderID
,tblOrders.freightCharge
HAVING tblOrders.orderID = tblOrderDetails.orderID
and tblOrders.freightCharge >
MIN(tblOrderDetails.unitPriceOnOrderDate)
)
The outer query has:
tblOrders
tblCustomers
tblOrderDetails
The subquery has:
tblOrders
tblOrderDetails
Note: In the outer query, tblOrder and tblCustomers are JOINed on
customerID = orderID. Is this correct (I can't imagine it would be)?
I changed it in example below in the hopes I was interpreting things
correctly.
--
In order for an EXISTS predicate to function properly (AFAIK, anyway),
the subquery must be correlated with the outer query.
I cannot spot the correlation in the above SQL. Without Table
Aliases, the query optimizer has no way of linking the subquery to the
outer query.
The following is the original Query now reformatted with Table Aliases
and a WHERE clause correlating the subquery and the outer query.
SELECT C1.companyName
,O1.orderID
,O1.freightCharge
,OD1.unitPriceOnOrderDate
FROM tblCustomers AS C1
INNER JOIN
tblOrders AS O1
ON C1.customerID = O1.customerID
INNER JOIN
tblOrderDetails AS OD1
ON O1.orderID = OD1.orderID
WHERE EXISTS
(SELECT O2.orderID
FROM tblOrders AS O2
INNER JOIN
tblOrderDetails AS OD2
ON O2.orderID = OD2.orderID
WHERE O1.orderID = O2.orderID
GROUP BY O2.orderID
,OD2.orderID
,O2.freightCharge
HAVING O2.orderID = OD2.orderID
and O2.freightCharge >
MIN(OD2.unitPriceOnOrderDate)
)
Note: Notice how the WHERE clause refers to a table in the outer query
by using the alias of the table in the outer query.
Note: I am making a fairly huge guess on the proper correlation in the
WHERE clause of the subquery. Pleaes take it for what it is meant to
be, a hint directing you toward a solution. IOW, this is definitely
not tested.
Sincerely,
Chris O.
PS The link http://www.aspfaq.com/etiquette.asp?id=5006, is excellent
when it comes to detailing how to provide the information that will
best enable others to answer your questions.|||Did it occur to you that "unit_price_on_order_date" is a query and
not a data element? WHERE IS THE IMPLIED HISTORY PRICE TABLE?
Did it occur to you that a customer_id should NEVER be equal to an
order_id?
What you posted is a screwed up mess.
Then you to spit on the people that ate helping you for free, you never
posted DDL.
Would you like to try again? With DDL? With specs that can be
programmed from?|||On 29 Oct 2005 11:47:10 -0700, A_StClaire_@.hotmail.com wrote:
(snip)
>idea is to show all orders whose freight charge (contained in
>tblOrders) exceeds the unit price (contained in tblOrderDetails) of any
>product in that order.
Hi A_StClaire_,
Try if the query below works:
SELECT tblCustomers.companyName, tblOrders.orderID,
tblOrders.freightCharge, tblOrderDetails.unitPriceOnOrderDate
FROM tblCustomers
JOIN tblOrders
ON tblCustomers.customerID = tblOrders.orderID
JOIN tblOrderDetails
ON tblOrders.orderID = tblOrderDetails.orderID
WHERE tblOrders.freightCharge > unitPriceOnOrderDate
(Untested - see www.aspfaq.com/5006 if you prefer a tested solution, or
if the query above doesn't meet your expectations)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Did it occur to you that "unit_price_on_order_date" is a query and
> not a data element? WHERE IS THE IMPLIED HISTORY PRICE TABLE?
> Did it occur to you that a customer_id should NEVER be equal to an
> order_id?
> What you posted is a screwed up mess.
No, what you posted is a screwed up mess. For crying out load, the
guys says that he is a begeinner. Beginners are permitted to make mistakes.
And they should be permitted to make mistakes, without having to be
insulted by morons like you.

> Then you to spit on the people that ate helping you for free, you never
> posted DDL.
>
How can he post DDL when you never explain what it is? And how the hell
can you accuse someone for spitting when he is asking politely?

> Would you like to try again?
The risk is that he will never try again, because he was turned off by
your reply. Is that what you want?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:f1t7m192si2fd8h6ftv78lvotge2mg4h2i@.
4ax.com...
> On 29 Oct 2005 11:47:10 -0700, A_StClaire_@.hotmail.com wrote:
> (snip)
any
> Hi A_StClaire_,
> Try if the query below works:
> SELECT tblCustomers.companyName, tblOrders.orderID,
> tblOrders.freightCharge, tblOrderDetails.unitPriceOnOrderDate
> FROM tblCustomers
> JOIN tblOrders
> ON tblCustomers.customerID = tblOrders.orderID
> JOIN tblOrderDetails
> ON tblOrders.orderID = tblOrderDetails.orderID
> WHERE tblOrders.freightCharge > unitPriceOnOrderDate
> (Untested - see www.aspfaq.com/5006 if you prefer a tested solution,
or
> if the query above doesn't meet your expectations)
> Best, Hugo
> --
>
Hugo,
The above has:

> ON tblCustomers.customerID = tblOrders.orderID
I am still thinking that customerID cannot equal orderID.
Sincerely,
Chris O.|||
> The outer query has:
> tblOrders
> tblCustomers
> tblOrderDetails
> The subquery has:
> tblOrders
> tblOrderDetails
> --
> Note: In the outer query, tblOrder and tblCustomers are JOINed on
> customerID = orderID. Is this correct (I can't imagine it would be)?
> I changed it in example below in the hopes I was interpreting things
> correctly.
> --
you are absolutely right, Chris.

> In order for an EXISTS predicate to function properly (AFAIK, anyway),
> the subquery must be correlated with the outer query.
> I cannot spot the correlation in the above SQL. Without Table
> Aliases, the query optimizer has no way of linking the subquery to the
> outer query.
>
> The following is the original Query now reformatted with Table Aliases
> and a WHERE clause correlating the subquery and the outer query.
>
> SELECT C1.companyName
> ,O1.orderID
> ,O1.freightCharge
> ,OD1.unitPriceOnOrderDate
> FROM tblCustomers AS C1
> INNER JOIN
> tblOrders AS O1
> ON C1.customerID = O1.customerID
> INNER JOIN
> tblOrderDetails AS OD1
> ON O1.orderID = OD1.orderID
> WHERE EXISTS
> (SELECT O2.orderID
> FROM tblOrders AS O2
> INNER JOIN
> tblOrderDetails AS OD2
> ON O2.orderID = OD2.orderID
> WHERE O1.orderID = O2.orderID
> GROUP BY O2.orderID
> ,OD2.orderID
> ,O2.freightCharge
> HAVING O2.orderID = OD2.orderID
> and O2.freightCharge >
> MIN(OD2.unitPriceOnOrderDate)
> )
> Note: Notice how the WHERE clause refers to a table in the outer query
> by using the alias of the table in the outer query.
> Note: I am making a fairly huge guess on the proper correlation in the
> WHERE clause of the subquery. Pleaes take it for what it is meant to
> be, a hint directing you toward a solution. IOW, this is definitely
> not tested.
you derived exactly the intended solution. I am not very clear on many
aspects of SQL including aliases and it's obvious I have a lot to
learn.

> Sincerely,
> Chris O.
> PS Although meant for microsoft.pulic.sqlserver.programming, the
> following link is still applicable for microsoft.pulic.access.queries:
> http://www.aspfaq.com/etiquette.asp?id=5006, when it comes to
> providing the information that will best enable others to answer your
> question.
thx again for your help. I will definitely get up to speed from an
etiquette point of view.|||
> Hugo,
> The above has:
>
> I am still thinking that customerID cannot equal orderID.
>
> Sincerely,
> Chris O.
yes. it would be a very interesting query that could operate in the
manner specified by myself initially.

Another n00b select statement problem...

When I try to use this select statement:

SELECT main.title, main.URL, main.description, main.city FROM main ORDER BY city WHERE (((main.active)=True));

I get a: Syntax error (missing operator) in query expression 'city WHERE (((main.active)=True))'

But, if I try this:

SELECT main.title, main.URL, main.description, main.city FROM main WHERE (((main.active)=True));

or this:

SELECT main.title, main.URL, main.description, main.city FROM main ORDER BY city;

They both work as expected. How do I combine them effectively?

Also, when I try the select statement below:

SELECT main.title, main.url, main.description, main.city
FROM main
WHERE (((main.category)="educ") AND ((main.category_2)="elem") AND ((main.active)=True));

I get a: Unterminated string constant Error (probably because of the double quotes?)

So how can this one be formatted to work without the quotes? (I have tried just removing them to no avail)

Thank You for your help. I am very happy to have found this resource (even though I couldn't find a thread with a solution to a similar problem)for your first problem, ORDER BY must follow WHERE

for your second problem the string delimiter is the single quote, not the double quote|||Originally posted by r937
for your first problem, ORDER BY must follow WHERE

for your second problem the string delimiter is the single quote, not the double quote

Thank you, first problem solved (Order of operations did the trick)

However, the second problem remains:

rs.Open "SELECT main.title, main.url, main.description, main.city
FROM main
WHERE (((main.category_2)='elem'));", conn%>

When you said the string delimiter is the single quote, I am assuming that means to bracket the elem by single quotes instead of double. The above select statement still gives me a Unterminated string constant error.|||have a look at this: Getting Your Quotes Right In SQL For ASP (http://www.webdevelopersjournal.com/articles/quotes_sql_asp.html)|||Could this be because you are using VBScript which requires end of line continuation markers something more like this? :-

rs.Open "SELECT main.title, main.url, main.description, main.city" _
& " FROM main" _
& " WHERE (((main.category_2)='elem'));", conn%>

Thursday, March 8, 2012

another freetexttable question

i'm trying to usr two (or more) columns in the catalog in the select.
I can get all: FREETEXTTABLE(usr, * , @.term)
or 1 column: FREETEXTTABLE(usr, usrCompany , @.term)
but 2 or more: FREETEXTTABLE(usr, "usrCompany, usrBusDesc" , @.term)
doesn't work. I've seen in the book's on line that it can be done, but have
not found an example.
Any help appreciated! ...and Happy New Year!
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
Try
select * From FREETEXTTABLE(usr, (usrCompany, usrBusDesc) , @.term)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:5CB29272-DA9E-484F-BECE-CD63610A6736@.microsoft.com...
> i'm trying to usr two (or more) columns in the catalog in the select.
> I can get all: FREETEXTTABLE(usr, * , @.term)
> or 1 column: FREETEXTTABLE(usr, usrCompany , @.term)
> but 2 or more: FREETEXTTABLE(usr, "usrCompany, usrBusDesc" , @.term)
> doesn't work. I've seen in the book's on line that it can be done, but
> have
> not found an example.
> Any help appreciated! ...and Happy New Year!
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
|||thank's for responding and happy new year,
I did try that, but i keep getting: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Hilary Cotter" wrote:

> Try
> select * From FREETEXTTABLE(usr, (usrCompany, usrBusDesc) , @.term)
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:5CB29272-DA9E-484F-BECE-CD63610A6736@.microsoft.com...
>
>
|||Is this SQL 2000? You can only do this in SQL 2005. In SQL 2000 its one
column or all columns (when you use a *)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:2AF649A4-3375-4772-9DC9-E96FDC365309@.microsoft.com...[vbcol=seagreen]
> thank's for responding and happy new year,
> I did try that, but i keep getting: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '('.
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
>
> "Hilary Cotter" wrote:
|||it's 2000, that's the answer!
Thank You!
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Hilary Cotter" wrote:

> Is this SQL 2000? You can only do this in SQL 2005. In SQL 2000 its one
> column or all columns (when you use a *)
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:2AF649A4-3375-4772-9DC9-E96FDC365309@.microsoft.com...
>
>

Another execution plan question

SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
t1.col3=t2.col3
The total number of rows in Table1 are just 100. But the estimated row count
shows 75,000.
Its a production environment and probably cannot drop any procedure caches .
How can I make the estimated row count to show me the actual count or
atleast drop it to within the 100s range. Table1 has only 1 clustered index
on (col2,col3) . IF i have to use hints, what hints can i use and if not,
what else can i do to reflect the actual count"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> t1.col3=t2.col3
> The total number of rows in Table1 are just 100. But the estimated row
count
> shows 75,000.
> Its a production environment and probably cannot drop any procedure caches
.
> How can I make the estimated row count to show me the actual count or
> atleast drop it to within the 100s range. Table1 has only 1 clustered
index
> on (col2,col3) . IF i have to use hints, what hints can i use and if not,
> what else can i do to reflect the actual count
How many rows are there in table 2? If there is a one to many relationship
then a single row in table 1 will be counted for every row in table 2 it is
paired with.
If you only want a row to appear once from table 1 then change the query to
SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
t1.col3=t2.col3
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003|||You didnt follow my question Bob..
When i talk about estimated row count, I mean when i click on the operators
in the execution plan under the clustered index scan of table1 which only
has 100 rows but the estimated row count shown there is around 75000.
Probably at one point in time it was that much and hence the adhoc query
plan is reflecting that. I would like to change it to show me atleast in a
100s range...
"Bob Simms" <bob_simms@.hotmail.com> wrote in message
news:AB7fb.820$Wm6.21@.news-binary.blueyonder.co.uk...
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > t1.col3=t2.col3
> >
> > The total number of rows in Table1 are just 100. But the estimated row
> count
> > shows 75,000.
> > Its a production environment and probably cannot drop any procedure
caches
> .
> > How can I make the estimated row count to show me the actual count or
> > atleast drop it to within the 100s range. Table1 has only 1 clustered
> index
> > on (col2,col3) . IF i have to use hints, what hints can i use and if
not,
> > what else can i do to reflect the actual count
> How many rows are there in table 2? If there is a one to many
relationship
> then a single row in table 1 will be counted for every row in table 2 it
is
> paired with.
> If you only want a row to appear once from table 1 then change the query
to
> SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2
and
> t1.col3=t2.col3
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003
>|||Did you try updating statistics with fullscan. I'm not sure it'll help, but worth a try...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> t1.col3=t2.col3
> The total number of rows in Table1 are just 100. But the estimated row count
> shows 75,000.
> Its a production environment and probably cannot drop any procedure caches .
> How can I make the estimated row count to show me the actual count or
> atleast drop it to within the 100s range. Table1 has only 1 clustered index
> on (col2,col3) . IF i have to use hints, what hints can i use and if not,
> what else can i do to reflect the actual count
>|||I did all that... As I was hoping the plan might change...but no luck...
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OzlahhXiDHA.2248@.TK2MSFTNGP12.phx.gbl...
> Did you try updating statistics with fullscan. I'm not sure it'll help,
but worth a try...
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > t1.col3=t2.col3
> >
> > The total number of rows in Table1 are just 100. But the estimated row
count
> > shows 75,000.
> > Its a production environment and probably cannot drop any procedure
caches .
> > How can I make the estimated row count to show me the actual count or
> > atleast drop it to within the 100s range. Table1 has only 1 clustered
index
> > on (col2,col3) . IF i have to use hints, what hints can i use and if
not,
> > what else can i do to reflect the actual count
> >
> >
>|||On Thu, 2 Oct 2003 21:16:28 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
>t1.col3=t2.col3
>The total number of rows in Table1 are just 100. But the estimated row count
>shows 75,000.
>Its a production environment and probably cannot drop any procedure caches .
>How can I make the estimated row count to show me the actual count or
>atleast drop it to within the 100s range. Table1 has only 1 clustered index
>on (col2,col3) . IF i have to use hints, what hints can i use and if not,
>what else can i do to reflect the actual count
I seldom even look at the estimated numbers, and even more seldom am
able to make any sense out of them. I suspect many, many bugs, as
well as unexplained complexities, in the numbers displayed.
When you actually run the query, what kind of statistics do you get?
And, how many rows get returned?
Joshua Stern|||you have indexes on the columns in both t1 and t2 AND you have updated
statistics with FULLSCAN on both tables? And you still are getting widely
off estimated row counts showing in the graphical showplan? I just want to
make sure I understand...
that would be bit wierd to be off SOO much on such simple queries if the
columns are indexed and you did FULLSCAN...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e9cqgmXiDHA.1796@.TK2MSFTNGP10.phx.gbl...
> I did all that... As I was hoping the plan might change...but no luck...
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OzlahhXiDHA.2248@.TK2MSFTNGP12.phx.gbl...
> > Did you try updating statistics with fullscan. I'm not sure it'll help,
> but worth a try...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > t1.col3=t2.col3
> > >
> > > The total number of rows in Table1 are just 100. But the estimated row
> count
> > > shows 75,000.
> > > Its a production environment and probably cannot drop any procedure
> caches .
> > > How can I make the estimated row count to show me the actual count or
> > > atleast drop it to within the 100s range. Table1 has only 1 clustered
> index
> > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> not,
> > > what else can i do to reflect the actual count
> > >
> > >
> >
> >
>|||On Thu, 2 Oct 2003 21:16:28 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
>t1.col3=t2.col3
>The total number of rows in Table1 are just 100. But the estimated row count
>shows 75,000.
>Its a production environment and probably cannot drop any procedure caches .
>How can I make the estimated row count to show me the actual count or
>atleast drop it to within the 100s range. Table1 has only 1 clustered index
>on (col2,col3) . IF i have to use hints, what hints can i use and if not,
>what else can i do to reflect the actual count
You've got no where clause.
Actually, if it's a clustered index, and the 100 rows remaining are
spread over a space that used to have 75,000 rows, maybe it's telling
you that it has to physically scan the space that would normally take
75k rows? Maybe someone here with more experience with these plans
and clustered indexes can confirm this.
J.|||Dropped all indexes and recreated them to resolve this.. It was weird. The
adhoc query plan was still being cached I guess.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:OBan2MeiDHA.1200@.TK2MSFTNGP09.phx.gbl...
> you have indexes on the columns in both t1 and t2 AND you have updated
> statistics with FULLSCAN on both tables? And you still are getting widely
> off estimated row counts showing in the graphical showplan? I just want to
> make sure I understand...
> that would be bit wierd to be off SOO much on such simple queries if the
> columns are indexed and you did FULLSCAN...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e9cqgmXiDHA.1796@.TK2MSFTNGP10.phx.gbl...
> > I did all that... As I was hoping the plan might change...but no luck...
> >
> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:OzlahhXiDHA.2248@.TK2MSFTNGP12.phx.gbl...
> > > Did you try updating statistics with fullscan. I'm not sure it'll
help,
> > but worth a try...
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > > t1.col3=t2.col3
> > > >
> > > > The total number of rows in Table1 are just 100. But the estimated
row
> > count
> > > > shows 75,000.
> > > > Its a production environment and probably cannot drop any procedure
> > caches .
> > > > How can I make the estimated row count to show me the actual count
or
> > > > atleast drop it to within the 100s range. Table1 has only 1
clustered
> > index
> > > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> > not,
> > > > what else can i do to reflect the actual count
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi Hassan.
I think that Bob is probably on the money - I've carefully re-read your
original post, what Bob's responded with & the other posts too, but I think
Bob most likely right.
Does table2 have 75000 rows? If so, I think that you won't get the estimated
rowcount down below this because, critically, your query is selecting
t2.col1 which is from the right hand side (table2) of the join statement.
This means that the estimated rows will be however many rows are in table2 &
as you have no where clause (either on table1 or table2) there is no way for
the optimizer to short cut a full scan of table2.col1. If table2 has 75000
rows, then the only valid estimate is 75000 rows.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e#IhICXiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> You didnt follow my question Bob..
> When i talk about estimated row count, I mean when i click on the
operators
> in the execution plan under the clustered index scan of table1 which only
> has 100 rows but the estimated row count shown there is around 75000.
> Probably at one point in time it was that much and hence the adhoc query
> plan is reflecting that. I would like to change it to show me atleast in a
> 100s range...
>
> "Bob Simms" <bob_simms@.hotmail.com> wrote in message
> news:AB7fb.820$Wm6.21@.news-binary.blueyonder.co.uk...
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > t1.col3=t2.col3
> > >
> > > The total number of rows in Table1 are just 100. But the estimated row
> > count
> > > shows 75,000.
> > > Its a production environment and probably cannot drop any procedure
> caches
> > .
> > > How can I make the estimated row count to show me the actual count or
> > > atleast drop it to within the 100s range. Table1 has only 1 clustered
> > index
> > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> not,
> > > what else can i do to reflect the actual count
> >
> > How many rows are there in table 2? If there is a one to many
> relationship
> > then a single row in table 1 will be counted for every row in table 2 it
> is
> > paired with.
> >
> > If you only want a row to appear once from table 1 then change the query
> to
> > SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2
> and
> > t1.col3=t2.col3
> >
> >
> >
> > --
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003
> >
> >
>|||Table T2 has 20 millions rows and the estimated row count for that shows
around the same. But T1 has only 100 rows and the estimated row count for T1
showed 75000. I dropped and recreated all indexes and it worked fine now.
Just some plan being cached I guess.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%234MVFakiDHA.2536@.TK2MSFTNGP10.phx.gbl...
> Hi Hassan.
> I think that Bob is probably on the money - I've carefully re-read your
> original post, what Bob's responded with & the other posts too, but I
think
> Bob most likely right.
> Does table2 have 75000 rows? If so, I think that you won't get the
estimated
> rowcount down below this because, critically, your query is selecting
> t2.col1 which is from the right hand side (table2) of the join statement.
> This means that the estimated rows will be however many rows are in table2
&
> as you have no where clause (either on table1 or table2) there is no way
for
> the optimizer to short cut a full scan of table2.col1. If table2 has 75000
> rows, then the only valid estimate is 75000 rows.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e#IhICXiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> > You didnt follow my question Bob..
> >
> > When i talk about estimated row count, I mean when i click on the
> operators
> > in the execution plan under the clustered index scan of table1 which
only
> > has 100 rows but the estimated row count shown there is around 75000.
> > Probably at one point in time it was that much and hence the adhoc query
> > plan is reflecting that. I would like to change it to show me atleast in
a
> > 100s range...
> >
> >
> > "Bob Simms" <bob_simms@.hotmail.com> wrote in message
> > news:AB7fb.820$Wm6.21@.news-binary.blueyonder.co.uk...
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > > t1.col3=t2.col3
> > > >
> > > > The total number of rows in Table1 are just 100. But the estimated
row
> > > count
> > > > shows 75,000.
> > > > Its a production environment and probably cannot drop any procedure
> > caches
> > > .
> > > > How can I make the estimated row count to show me the actual count
or
> > > > atleast drop it to within the 100s range. Table1 has only 1
clustered
> > > index
> > > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> > not,
> > > > what else can i do to reflect the actual count
> > >
> > > How many rows are there in table 2? If there is a one to many
> > relationship
> > > then a single row in table 1 will be counted for every row in table 2
it
> > is
> > > paired with.
> > >
> > > If you only want a row to appear once from table 1 then change the
query
> > to
> > > SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on
t1.col2=t2.col2
> > and
> > > t1.col3=t2.col3
> > >
> > >
> > >
> > > --
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003
> > >
> > >
> >
> >
>|||On Fri, 3 Oct 2003 17:14:09 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>Dropped all indexes and recreated them to resolve this.. It was weird. The
>adhoc query plan was still being cached I guess.
Did you drop a clustered index on table1?
J.

Wednesday, March 7, 2012

Another 101 question

With a plain select if no rows are returned then the row count is 0, but
with a select that assigns to a variable you will get a rowcount of one even
though no actual row was found. I am guessing that is because of assigning
to a variable SQL will return an empty result set, which is a result set of
1, is this correct?
i.e. SET @.mycol =
(SELECT col FROM myTable WHERE id = 1)
Under these circumstances is the best technique to just test the variable
@.mycol for a null value
OR write the select like:
IF EXISTS
(SELECT mycol FROM myTable
WHERE myID = 3)
BEGIN
SET @.mycol =
(SELECT mycol FROM myTable
WHERE myID = 3)
PRINT '@.mycol : ' + CAST(mycol as varchar(15))
END
ELSE
PRINT 'ROW DOES NOT EXIST'
Or is there an even better and/or more professional way to do it?Thank you! I was making my self nuts with the what if's, it seemed that
going beyond validating the current entry could turn into a never ending
task. :)
Except for the news groups I am learning in a vacuum, it is not like
learning in maintenance of a production environment where you get to see
what others have done.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eb6rNDKvFHA.3896@.TK2MSFTNGP15.phx.gbl...
> Thanks Erland, I really should have mentioned that.
> Dazed,
> If you have a Unique constraint you should not have to test for
> duplicates when selecting the values out. The constraint will make sure
> there are no duplicates in the first place.
> --
> Andrew J. Kelly SQL MVP
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96D61468ABF3Yazorman@.127.0.0.1...
>|||What is the desired behavior? DO you simply want to know if one or more
rows exist or not? If so then always use EXISTS.
Andrew J. Kelly SQL MVP
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:eXq$4VHvFHA.2072@.TK2MSFTNGP14.phx.gbl...
> With a plain select if no rows are returned then the row count is 0, but
> with a select that assigns to a variable you will get a rowcount of one
> even though no actual row was found. I am guessing that is because of
> assigning to a variable SQL will return an empty result set, which is a
> result set of 1, is this correct?
> i.e. SET @.mycol =
> (SELECT col FROM myTable WHERE id = 1)
> Under these circumstances is the best technique to just test the variable
> @.mycol for a null value
> OR write the select like:
> IF EXISTS
> (SELECT mycol FROM myTable
> WHERE myID = 3)
> BEGIN
> SET @.mycol =
> (SELECT mycol FROM myTable
> WHERE myID = 3)
> PRINT '@.mycol : ' + CAST(mycol as varchar(15))
> END
> ELSE
> PRINT 'ROW DOES NOT EXIST'
> Or is there an even better and/or more professional way to do it?
>|||When you use a scalar subquery, you can get a one-row, one-column table
that is converted to a scalar; you can get an empty table that is
converted to a NULL; you can get a multi-row, one-column table that
gives a cardinality error when you try to put it into a scalar.|||In this paticular situation I want the field value. I was wondering if it
was better to just do the select and test the variable for a null or do a
EXISTS SELECT and then if it does exist select into the variable.
I'm just learning and trying to find the best way to build a mouse trap,
I've found this news group very helpful. i.e. I had a 130 line procedure
yesterday that was cut down to about 30 from information obtained from the
group. There are a lot of ways to get things to work, some are a lot better
than others. I'm looking for the right ways.
Thank you!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ObA0rlHvFHA.2504@.TK2MSFTNGP15.phx.gbl...
> What is the desired behavior? DO you simply want to know if one or more
> rows exist or not? If so then always use EXISTS.
> --
> Andrew J. Kelly SQL MVP
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:eXq$4VHvFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||OK then there is no need for an EXISTS first. You can do this several ways
like:
SET @.mycol = (SELECT col FROM myTable WHERE id = 1)
or
SELECT @.mycol = Col FROM myTable WHERE id = 1
IF @.myCol IS NOT NULL
BEGIN
-- Do your thing here
END
ELSE
...
Also make sure that there will only be at most 1 row returned. As long as
ID is a unique value you should be ok.
Andrew J. Kelly SQL MVP
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uQq5RzHvFHA.3548@.tk2msftngp13.phx.gbl...
> In this paticular situation I want the field value. I was wondering if it
> was better to just do the select and test the variable for a null or do a
> EXISTS SELECT and then if it does exist select into the variable.
> I'm just learning and trying to find the best way to build a mouse trap,
> I've found this news group very helpful. i.e. I had a 130 line procedure
> yesterday that was cut down to about 30 from information obtained from the
> group. There are a lot of ways to get things to work, some are a lot
> better than others. I'm looking for the right ways.
> Thank you!!
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ObA0rlHvFHA.2504@.TK2MSFTNGP15.phx.gbl...
>|||Thank you again! id is unique, but I am checking if rowcount is > 1 anyways
and plan on throwing an error if it is. I plan on temporarily removing the
unique constraint and putting in a dupe record to test it.
Is that going too far?
Or is it a good idea to try to handle a situation that in theory should
never happen?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMkfpNIvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> OK then there is no need for an EXISTS first. You can do this several
> ways like:
> SET @.mycol = (SELECT col FROM myTable WHERE id = 1)
> or
> SELECT @.mycol = Col FROM myTable WHERE id = 1
>
> IF @.myCol IS NOT NULL
> BEGIN
> -- Do your thing here
> END
> ELSE
> ...
> Also make sure that there will only be at most 1 row returned. As long as
> ID is a unique value you should be ok.
> --
> Andrew J. Kelly SQL MVP
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:uQq5RzHvFHA.3548@.tk2msftngp13.phx.gbl...
>|||DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> Thank you again! id is unique, but I am checking if rowcount is > 1
> anyways and plan on throwing an error if it is. I plan on temporarily
> removing the unique constraint and putting in a dupe record to test it.
> Is that going too far?
> Or is it a good idea to try to handle a situation that in theory should
> never happen?
OK, now we are in for a real treat! I will show you how to do it, and if
that does not convince you that are going too far, nothing will. :-)
Andy showed you two ways, but they are a little different, which he failed
to tell. Let's look at them again:
0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
many rows -> You will get an error, "subquery returned more than one value".
0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
1 row -> @.mycol assigned the value, @.@.rowcount = 1
many rows -> @.mycol gets the last value in the result set, which that
is undefined unless you have an ORDER BY. @.@.rowcount is
set to the number of matching rows.
Look at 0 rows again:
SELECT @.mycol = 4711
SELECT @.mycol = Col FROM myTable WHERE id = 1
If there is no row with id = 1, @.mycol will remain 4711, it will not be
set to NULL.
If you really want to check for duplicates, and handle the situation
yourself, it is the SELECT assignment you want to use.
If you keep the constraints, which you should unless you have very good
reasons, the SET method is a little safer. Then again, if you know how
SELECT behaves you can be careful make sure variable is NULL before
you use it. (Yet then again, that is a trap that even season T-SQL
programmers fall into, every now and then!)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, I really should have mentioned that.
Dazed,
If you have a Unique constraint you should not have to test for
duplicates when selecting the values out. The constraint will make sure
there are no duplicates in the first place.
Andrew J. Kelly SQL MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D61468ABF3Yazorman@.127.0.0.1...
> DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> OK, now we are in for a real treat! I will show you how to do it, and if
> that does not convince you that are going too far, nothing will. :-)
> Andy showed you two ways, but they are a little different, which he failed
> to tell. Let's look at them again:
>
> 0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
> 1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
> many rows -> You will get an error, "subquery returned more than one
> value".
>
> 0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
> 1 row -> @.mycol assigned the value, @.@.rowcount = 1
> many rows -> @.mycol gets the last value in the result set, which that
> is undefined unless you have an ORDER BY. @.@.rowcount is
> set to the number of matching rows.
> Look at 0 rows again:
> SELECT @.mycol = 4711
> SELECT @.mycol = Col FROM myTable WHERE id = 1
> If there is no row with id = 1, @.mycol will remain 4711, it will not be
> set to NULL.
> If you really want to check for duplicates, and handle the situation
> yourself, it is the SELECT assignment you want to use.
> If you keep the constraints, which you should unless you have very good
> reasons, the SET method is a little safer. Then again, if you know how
> SELECT behaves you can be careful make sure variable is NULL before
> you use it. (Yet then again, that is a trap that even season T-SQL
> programmers fall into, every now and then!)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Thank you for the SET/SELECT behavior. Your reply seemed to imply (reading
in between the lines) that since the the id is UNIQUE don't bother to check
for multiple rows, SQL will error anyways in the unlikely event.
If there are duplicates in a unique column, then there is data corruption
anyways, pretty messages aren't really going to help the application faling,
it is time to contact the DBA to see why the data is corrupt.
Is that right?
I'm going nuts creating a procedure that checks for both bad and/or
duplicate data being passed into it and handling for corrupt database
information that should not happen. Seems like opening pandora's box when I
try to code pretty returns to notify the application that the database is
corrupt.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D61468ABF3Yazorman@.127.0.0.1...
> DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> OK, now we are in for a real treat! I will show you how to do it, and if
> that does not convince you that are going too far, nothing will. :-)
> Andy showed you two ways, but they are a little different, which he failed
> to tell. Let's look at them again:
>
> 0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
> 1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
> many rows -> You will get an error, "subquery returned more than one
> value".
>
> 0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
> 1 row -> @.mycol assigned the value, @.@.rowcount = 1
> many rows -> @.mycol gets the last value in the result set, which that
> is undefined unless you have an ORDER BY. @.@.rowcount is
> set to the number of matching rows.
> Look at 0 rows again:
> SELECT @.mycol = 4711
> SELECT @.mycol = Col FROM myTable WHERE id = 1
> If there is no row with id = 1, @.mycol will remain 4711, it will not be
> set to NULL.
> If you really want to check for duplicates, and handle the situation
> yourself, it is the SELECT assignment you want to use.
> If you keep the constraints, which you should unless you have very good
> reasons, the SET method is a little safer. Then again, if you know how
> SELECT behaves you can be careful make sure variable is NULL before
> you use it. (Yet then again, that is a trap that even season T-SQL
> programmers fall into, every now and then!)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>

Tuesday, March 20, 2012

another way to write this query?

another way to write this query?

another way to write this query?

Monday, March 19, 2012

Another SelectCommand Distinct problem

Sunday, March 11, 2012

Another question (IN keyword)

another question

another query question

Another Query Question

Another Problem with variables

Another Newb SQL Question

Another n00b select statement problem...

Thursday, March 8, 2012

another freetexttable question

Another execution plan question

Wednesday, March 7, 2012

Another 101 question

SQL server connection

Blog Archive

About Me