about "trailing Space" in the record

Hi, there;
We know that: Both select * from mytable where column1="data" and select * from mytable where column1="data " give us same result. (Please note the spaces in second query) This means the trailing space doesn't affect the query result.

How can I make SQL to return different result?

Thanks.

That is the result of the ANSI standard.

One way that you could use to determine if the values, including trailing blanks, are different, is to use the datalength() functions. Something like this:

Code Snippet

DECLARE
@.MyVar1 varchar(20),
@.MyVar2 varchar(20)

SELECT
@.MyVar1 = 'data',
@.MyVar2 = 'data '

IF datalength( @.MyVar1 ) = datalength( @.MyVar2 )
PRINT 'Values are the same'
ELSE
PRINT 'Values are NOT the same'

|||Thanks Arnie.

My case is a bit different. I need to run a sql statement (DELETE FROM MYTABLE WHERE key='KEYVALUE') from an application. The data in the table is imported from raining data. Some data has trailing space. Because of the ANSI standard, 'KEYVALUE ' will be deleted if I run that statement which is not I want.

I just wonder if there is a switch to we can turn it on/off to make it different.|||

Perhaps this will work for you:

WHERE ( KeyValue = 'KeyValue'

AND datalength( KeyValue ) = datalength( 'KeyValue' )
)

|||Thanks. That will do.

a where case question

I need to change the criteria of the select so that if @.thstype is 1 then
where a =b else a<> b. This is in a stored proc...
given declare @.thsType as INT
set @.thsType = 1
select *
from t1,t2,t2,...
where u.unvid case @.thsType When 1 then = else <> end @.thsUnvID
and u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kesYou were so close :-)
Use Northwind
DECLARE @.Test INT
SET @.test = 1
Select *
from customers where customerID =
(CASE WHEN @.Test = 1 THEN 'ALFKI' ELSE '###' END)
HTH, Jens Suessmeyer
http://www.sqlserver2005.de
--
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:7178EEFF-4304-4871-8138-7F2DF1A2D5BC@.microsoft.com...
>I need to change the criteria of the select so that if @.thstype is 1 then
> where a =b else a<> b. This is in a stored proc...
> given declare @.thsType as INT
> set @.thsType = 1
> select *
> from t1,t2,t2,...
> where u.unvid case @.thsType When 1 then = else <> end @.thsUnvID
> and u.unvtype = @.thsunvType
> and i1.idxhstdate = @.thsDate1
> and i2.idxhstdate = @.thsDate2
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||i'm not sure that will work. I really need something like this
Select *
from customers where
CASE WHEN @.Test = 1 THEN
customerID = 'ALFKI'
ELSE
customerID <> 'ALFKI'
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Jens Sü?meyer" wrote:

> You were so close :-)
> Use Northwind
> DECLARE @.Test INT
> SET @.test = 1
> Select *
> from customers where customerID =
> (CASE WHEN @.Test = 1 THEN 'ALFKI' ELSE '###' END)
> HTH, Jens Suessmeyer
> --
> http://www.sqlserver2005.de
> --
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:7178EEFF-4304-4871-8138-7F2DF1A2D5BC@.microsoft.com...
>
>|||Try this:
DECLARE @.Test INT
SET @.test = 1
Select *
from customers
where (@.Test = 1 and customerID = 'ALFKI') or
(@.Test <> 1 and customerID <> 'ALFKI' )
Perayu
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:8CBDCEFA-740D-4DA6-BA4D-18452E54FE1C@.microsoft.com...
> i'm not sure that will work. I really need something like this
> Select *
> from customers where
> CASE WHEN @.Test = 1 THEN
> customerID = 'ALFKI'
> ELSE
> customerID <> 'ALFKI'
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
>
> "Jens Smeyer" wrote:
>|||well.........., yes you have an answer and thank you!
However, it turned my mega proc of .5 sec into a 6 second proc.
I can write an if else and have two queries in the proc, but i'd like to
avoide that if i could.
Query below, for what is does it's very fast the condition needs to be in
the joined query at the bottom: (i added the or)
select
s.csistkcsisym,
s.csistksym1,
s.csistkcompany,
case s.csistkExchange when 'OTC' THEN 'Nasdaq' Else s.csistkExchange END as
Exchange,
u.unvName,
isnull(r.unvname, '********') as Sector,
case s.csistkActive when 0 then 'INACTIVE' else 'ACTIVE' END as status,
case h2.stkhstBuySell WHEN '' THEN 'N/A' WHEN 'B' THEN 'Buy' WHEN 'S' then
'Sell' ELSE h2.stkhstBuySell END as PFBuySell,
CASE
WHEN h2.stkhstBuySell = 'B' and h1.stkhstBuySell = 'S' THEN 'gnBK'
WHEN h2.stkhstBuySell = 'S' and h1.stkhstBuySell = 'B' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFBuySell,
h2.stkhstXO,
CASE
WHEN h2.stkhstXO = 'X' and h1.stkhstxo = 'O' THEN 'gnBK'
WHEN h2.stkhstXO = 'O' and h1.stkhstxo = 'X' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFXO,
CASE h2.stkhstLine WHEN 'A' THEN 'Above' WHEN 'B' THEN 'Below' ELSE 'N/A'
END as Trend,
CASE
WHEN h2.stkhstLine = 'A' AND h1.stkhstLine = 'B' THEN 'gnBK'
WHEN h2.stkhstLine = 'B' AND h1.stkhstLine = 'A' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFtrend,
case h2.stkhstRSBS WHEN '' THEN 'N/A' WHEN 'B' THEN 'Buy' WHEN 'S' then
'Sell' ELSE h2.stkhstRSBS END as RSBuySell,
CASE
WHEN h2.stkhstRSBS = 'B' AND h1.stkhstRSBS = 'S' THEN 'gnBK'
WHEN h2.stkhstRSBS = 'S' AND h1.stkhstRSBS = 'B' THEN 'rdBK'
ELSE 'wtBK'
END as NEWRSBuySell,
h2.stkhstRSXO,
CASE
WHEN h2.stkhstRSXO = 'X' AND h1.stkhstRSXO = 'O' THEN 'gnBK'
WHEN h2.stkhstRSXO = 'O' AND h1.stkhstRSXO = 'X' THEN 'rdBK'
ELSE 'wtBK'
END as NEWRSXO,
case When (h2.stkhst10wk - h2.stkhstClose) >= 0 then 'Below' else 'Above'
end as tenBeat,
CASE
WHEN ((h2.stkhst10wk - h2.stkhstClose) >= 0) AND ((h1.stkhst10wk -
h1.stkhstClose) < 0) then 'rdBK'
WHEN ((h1.stkhst10wk - h1.stkhstClose) >= 0) AND ((h2.stkhst10wk -
h2.stkhstClose) < 0) then 'gnBK'
ELSE 'wtBK'
END AS NEWtenBeat,
h2.stkhst10Wk,
h2.stkhstClose,
case
WHEN r.i2idxhstStatus is null
then (dbo.fn_rtnRSStatus(h2.stkhstRSBS,
h2.stkhstRSXO)+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine))*2
else ((dbo.fn_rtnBPStatus(r.i2idxhstStatus, r.i2idxhstPosChartPos)*50) +
(dbo.fn_rtnRsRStatus(r.i2idxhstRSBSXO)*25) +
(dbo.fn_rtnBPStatus(r.i2idxhstRSXOStatus, r.i2idxhstRSXOPos)*25) +
(dbo.fn_rtn10Status(r.i2idxhst10Status, r.i2idxhst10ChartPos)*50) +
(dbo.fn_rtnBPStatus(r.i2idxhstStatus, r.i2idxhstPosChartPos)*25) +
(dbo.fn_rtnBPStatus(r.i2idxhstRSXOStatus, r.i2idxhstRSXOPos)*25) )/4+
dbo.fn_rtnRSStatus(h2.stkhstRSBS, h2.stkhstRSXO)
+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine)
END as StockRate,
case
WHEN r.i2idxhstStatus is null
then (dbo.fn_rtnRSStatus(h2.stkhstRSBS,
h2.stkhstRSXO)+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine))*2
else ((dbo.fn_rtn10Status(r.i2idxhst10Status, r.i2idxhst10ChartPos)*50) +
(dbo.fn_rtnBPStatus(r.i2idxhstStatus, r.i2idxhstPosChartPos)*25) +
(dbo.fn_rtnBPStatus(r.i2idxhstRSXOStatus, r.i2idxhstRSXOPos)*25) )/2+
dbo.fn_rtnRSStatus(h2.stkhstRSBS, h2.stkhstRSXO)
+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine)
END as ShortTermRate,
case s.csistkActive when 0 then 'INACTIVE' else 'ACTIVE' END as status
from
csistk s
join unvmem m on m.unvmemCsiId = s.csistkcsisym
join unv u on u.unvID = m.unvmemUnvID
join stkhst h2 on h2.stkhstcsisym = s.csistkcsisym
join stkhst h1 on h1.stkhstcsisym = s.csistkcsisym
left join
(select u.unvname,
u.unvid,
m.unvmemcsiid,
i1.idxhstStatus as i1idxhstStatus,
i2.idxhstStatus as i2idxhstStatus,
i2.idxhstPosChartPos as i2idxhstPosChartPos,
i2.idxhstRSBSXO as i2idxhstRSBSXO,
i2.idxhstRSXOStatus as i2idxhstRSXOStatus,
i2.idxhstRSXOPos as i2idxhstRSXOPos,
i2.idxhst10Status as i2idxhst10Status,
i2.idxhst10ChartPos as i2idxhst10ChartPos
from unv u
join unvmem m on m.unvmemunvid = u.unvid
join idxhst i1 on i1.idxhstidxid = u.unvID
join idxhst i2 on i2.idxhstidxid = u.unvID
where @.thsOne = 1
and u.unvid <> @.thsUnvID
and u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2
or
@.thsOne<> 1
and u.unvid = @.thsUnvID
and u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2) r on r.unvmemcsiid = s.csistkcsisym
where u.unvid = @.thsUnvID
and h2.stkhstdate = @.thsDate2
and h1.stkhstdate = @.thsDate1
order by s.csistksym1
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Perayu" wrote:

> Try this:
> DECLARE @.Test INT
> SET @.test = 1
> Select *
> from customers
> where (@.Test = 1 and customerID = 'ALFKI') or
> (@.Test <> 1 and customerID <> 'ALFKI' )
> Perayu
>
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:8CBDCEFA-740D-4DA6-BA4D-18452E54FE1C@.microsoft.com...
>
>|||On Thu, 8 Sep 2005 13:41:02 -0700, WebBuilder451 wrote:

>well.........., yes you have an answer and thank you!
>However, it turned my mega proc of .5 sec into a 6 second proc.
>I can write an if else and have two queries in the proc, but i'd like to
>avoide that if i could.
>Query below, for what is does it's very fast the condition needs to be in
>the joined query at the bottom: (i added the or)
(snip)
> where @.thsOne = 1
> and u.unvid <> @.thsUnvID
> and u.unvtype = @.thsunvType
> and i1.idxhstdate = @.thsDate1
> and i2.idxhstdate = @.thsDate2
> or
> @.thsOne<> 1
> and u.unvid = @.thsUnvID
> and u.unvtype = @.thsunvType
> and i1.idxhstdate = @.thsDate1
> and i2.idxhstdate = @.thsDate2) r on r.unvmemcsiid = s.csistkcsisym
Hi WebBuilder451,
While this and/or condition will work, it is not very maintainable and
not very efficient either. Please remember that many people do not know
the precedence of evaluation for and and or by head. Just adding
brackets would make this code easier to understand!
But the code below, while equivalent, also has a better chance of being
able to use indexes:
where u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2
and ((@.thsOne = 1 and u.unvid <> @.thsUnvID)
or (@.thsOne <> 1 and u.unvid = @.thsUnvID))
) r on r.unvmemcsiid = s.csistkcsisym
If that doesn't solve your speed problem and speed is important for you,
than you'll have to duplicate your stored procedure to make two
versions: one for @.thsOne = 1 and one for @.thsOne <> 1. That will allow
SQL Server to create optimized execution plans for both situations.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

A way to "SELECT constraints FROM table"

is there any way to get the contraints or indexes of a table doing a SELECT QUERY?

I am using a VC++ application to do some changes on a Access DB and there is a contraint that as a random name... This would help me to find the constraint name automaticaly and change drop it as I want

hope someone knows a way to do thisSELECT * FROM MSysRelationships-PatP|||Thank you very very very much... you don't know how much did you help me :)

Finally someone gave me a good answer to my problem and it WORKS :D

A view that include a table from a database in another sql server

I need to create a view that contain a table from a database that reside in
another sql server.
The view is a simple select statement joining two tables; i.e. table A inner
joining table B that resides in different server. I hope this make sense.
I thank you in advance for any recommendations.
JamesCreate a linked server to the SQL Server containing table B or replicate the
data from table B to another table locally for the join.
HTH
Jerry
"jamesww7" <jamesww7@.discussions.microsoft.com> wrote in message
news:30796887-26D4-44E1-B3CF-AA4EB31C2894@.microsoft.com...
>I need to create a view that contain a table from a database that reside in
> another sql server.
> The view is a simple select statement joining two tables; i.e. table A
> inner
> joining table B that resides in different server. I hope this make sense.
> I thank you in advance for any recommendations.
> --
> James

A very simple select query question :-)

Hi everyone,
I'm having a small problem getting all the data I need from a table. I know
the answer is going to be really simple so if anyone could help I would be
very grateful.
Lets suppose I have a Books table. The Books table has an AuthorID column
to indicate the author of the book.
However, lets suppose that not all books have an author so the AuthorID may
be null.
Now, I need to Select all the books, including those that don't have an author.
In order to get the name of the Author, I'm doing a join with the Authors
table.
The Problem:
The Select query I'm using is only returning rows for books that
have Authors. If the Author ID is null then the row is simply ommited
So my question is, how do I get all the books out, even the ones that dont
have a corresponding record in the Authors table? See, simple isnt it? :-)
I'm using an Inner Join construct to join the two tables and I have a sneaking
suspicion that it is this that is causing the problem
The full text of the query would be something like:
SELECT *, Authors.FullName
FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
Or something like that.
I hope someone can help me. This is driving me nuts so I'd be deeply grateful
Kindest Regards
Simon
Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.
|||Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.
|||Simon Harvey wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I know
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID may
> be null.
> Now, I need to Select all the books, including those that don't have an author.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books that
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a sneaking
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grateful
> Kindest Regards
> Simon
Use an OUTER join:
SELECT Books.*,
Authors.FullName
FROM Books
LEFT OUTER JOIN Authors
ON Books.AuthorID = Authors.AuthorID ;
Books can have more than one author though, so I wouldn't expect to see
authorid in the Books table. More likely it belongs in a third table
that joins books to multiple authors (conceptually a many-to-many
relationship).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi,
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
=> Left Join will give you the whole list including author_id is null.
If you need only the record with authorid null use the below query...
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE (Books.AuthorID ='' OR Books.AuthorID IS NULL)
Thanks,
Sreejith
"Simon Harvey" wrote:

> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I know
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID may
> be null.
> Now, I need to Select all the books, including those that don't have an author.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books that
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a sneaking
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grateful
> Kindest Regards
> Simon
>
>
|||Hello David,

> Simon Harvey wrote:
> Use an OUTER join:
> SELECT Books.*,
> Authors.FullName
> FROM Books
> LEFT OUTER JOIN Authors
> ON Books.AuthorID = Authors.AuthorID ;
> Books can have more than one author though, so I wouldn't expect to
> see authorid in the Books table. More likely it belongs in a third
> table that joins books to multiple authors (conceptually a
> many-to-many relationship).
You are quite right. It was just a fictional example off the top of my head
though. The real thing is somewhat more complicated so I wanted to create
a really simple example that was conceptually the same. Same thing goes with
the whole Select * thing.
It was just a very quick example to save my poor wee fingers! :-)
Thanks to you all for offering advice. I am very grateful
Kindest Regards
Simon

A very simple select query question :-)

Hi everyone,
I'm having a small problem getting all the data I need from a table. I know
the answer is going to be really simple so if anyone could help I would be
very grateful.
Lets suppose I have a Books table. The Books table has an AuthorID column
to indicate the author of the book.
However, lets suppose that not all books have an author so the AuthorID may
be null.
Now, I need to Select all the books, including those that don't have an auth
or.
In order to get the name of the Author, I'm doing a join with the Authors
table.
The Problem:
The Select query I'm using is only returning rows for books that
have Authors. If the Author ID is null then the row is simply ommited
So my question is, how do I get all the books out, even the ones that dont
have a corresponding record in the Authors table? See, simple isnt it? :-)
I'm using an Inner Join construct to join the two tables and I have a sneaki
ng
suspicion that it is this that is causing the problem
The full text of the query would be something like:
SELECT *, Authors.FullName
FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
Or something like that.
I hope someone can help me. This is driving me nuts so I'd be deeply gratefu
l
Kindest Regards
SimonHi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Simon Harvey wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I kno
w
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID ma
y
> be null.
> Now, I need to Select all the books, including those that don't have an au
thor.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books tha
t
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a snea
king
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grate
ful
> Kindest Regards
> Simon
Use an OUTER join:
SELECT Books.*,
Authors.FullName
FROM Books
LEFT OUTER JOIN Authors
ON Books.AuthorID = Authors.AuthorID ;
Books can have more than one author though, so I wouldn't expect to see
authorid in the Books table. More likely it belongs in a third table
that joins books to multiple authors (conceptually a many-to-many
relationship).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
=> Left Join will give you the whole list including author_id is null.
If you need only the record with authorid null use the below query...
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE (Books.AuthorID ='' OR Books.AuthorID IS NULL)
Thanks,
Sreejith
"Simon Harvey" wrote:

> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I kno
w
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID ma
y
> be null.
> Now, I need to Select all the books, including those that don't have an au
thor.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books tha
t
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a snea
king
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grate
ful
> Kindest Regards
> Simon
>
>|||Hello David,

> Simon Harvey wrote:
>
> Use an OUTER join:
> SELECT Books.*,
> Authors.FullName
> FROM Books
> LEFT OUTER JOIN Authors
> ON Books.AuthorID = Authors.AuthorID ;
> Books can have more than one author though, so I wouldn't expect to
> see authorid in the Books table. More likely it belongs in a third
> table that joins books to multiple authors (conceptually a
> many-to-many relationship).
You are quite right. It was just a fictional example off the top of my head
though. The real thing is somewhat more complicated so I wanted to create
a really simple example that was conceptually the same. Same thing goes with
the whole Select * thing.
It was just a very quick example to save my poor wee fingers! :-)
Thanks to you all for offering advice. I am very grateful
Kindest Regards
Simon

A very simple select query question :-)

Hi everyone,
I'm having a small problem getting all the data I need from a table. I know
the answer is going to be really simple so if anyone could help I would be
very grateful.
Lets suppose I have a Books table. The Books table has an AuthorID column
to indicate the author of the book.
However, lets suppose that not all books have an author so the AuthorID may
be null.
Now, I need to Select all the books, including those that don't have an auth
or.
In order to get the name of the Author, I'm doing a join with the Authors
table.
The Problem:
The Select query I'm using is only returning rows for books that
have Authors. If the Author ID is null then the row is simply ommited
So my question is, how do I get all the books out, even the ones that dont
have a corresponding record in the Authors table? See, simple isnt it? :-)
I'm using an Inner Join construct to join the two tables and I have a sneaki
ng
suspicion that it is this that is causing the problem
The full text of the query would be something like:
SELECT *, Authors.FullName
FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
Or something like that.
I hope someone can help me. This is driving me nuts so I'd be deeply gratefu
l
Kindest Regards
SimonHi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Simon Harvey wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I kno
w
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID ma
y
> be null.
> Now, I need to Select all the books, including those that don't have an au
thor.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books tha
t
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a snea
king
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grate
ful
> Kindest Regards
> Simon
Use an OUTER join:
SELECT Books.*,
Authors.FullName
FROM Books
LEFT OUTER JOIN Authors
ON Books.AuthorID = Authors.AuthorID ;
Books can have more than one author though, so I wouldn't expect to see
authorid in the Books table. More likely it belongs in a third table
that joins books to multiple authors (conceptually a many-to-many
relationship).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
=> Left Join will give you the whole list including author_id is null.
If you need only the record with authorid null use the below query...
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE (Books.AuthorID ='' OR Books.AuthorID IS NULL)
Thanks,
Sreejith
"Simon Harvey" wrote:

> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I kno
w
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID ma
y
> be null.
> Now, I need to Select all the books, including those that don't have an au
thor.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books tha
t
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a snea
king
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grate
ful
> Kindest Regards
> Simon
>
>|||Hello David,

> Simon Harvey wrote:
>
> Use an OUTER join:
> SELECT Books.*,
> Authors.FullName
> FROM Books
> LEFT OUTER JOIN Authors
> ON Books.AuthorID = Authors.AuthorID ;
> Books can have more than one author though, so I wouldn't expect to
> see authorid in the Books table. More likely it belongs in a third
> table that joins books to multiple authors (conceptually a
> many-to-many relationship).
You are quite right. It was just a fictional example off the top of my head
though. The real thing is somewhat more complicated so I wanted to create
a really simple example that was conceptually the same. Same thing goes with
the whole Select * thing.
It was just a very quick example to save my poor wee fingers! :-)
Thanks to you all for offering advice. I am very grateful
Kindest Regards
Simon

A very simple select query question :-)

Hi everyone,
I'm having a small problem getting all the data I need from a table. I know
the answer is going to be really simple so if anyone could help I would be
very grateful.
Lets suppose I have a Books table. The Books table has an AuthorID column
to indicate the author of the book.
However, lets suppose that not all books have an author so the AuthorID may
be null.
Now, I need to Select all the books, including those that don't have an author.
In order to get the name of the Author, I'm doing a join with the Authors
table.
The Problem:
The Select query I'm using is only returning rows for books that
have Authors. If the Author ID is null then the row is simply ommited
So my question is, how do I get all the books out, even the ones that dont
have a corresponding record in the Authors table? See, simple isnt it? :-)
I'm using an Inner Join construct to join the two tables and I have a sneaking
suspicion that it is this that is causing the problem
The full text of the query would be something like:
SELECT *, Authors.FullName
FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
Or something like that.
I hope someone can help me. This is driving me nuts so I'd be deeply grateful
Kindest Regards
SimoHi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Simon Harvey wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I know
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID may
> be null.
> Now, I need to Select all the books, including those that don't have an author.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books that
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a sneaking
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grateful
> Kindest Regards
> Simon
Use an OUTER join:
SELECT Books.*,
Authors.FullName
FROM Books
LEFT OUTER JOIN Authors
ON Books.AuthorID = Authors.AuthorID ;
Books can have more than one author though, so I wouldn't expect to see
authorid in the Books table. More likely it belongs in a third table
that joins books to multiple authors (conceptually a many-to-many
relationship).
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
=> Left Join will give you the whole list including author_id is null.
If you need only the record with authorid null use the below query...
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE (Books.AuthorID ='' OR Books.AuthorID IS NULL)
Thanks,
Sreejith
"Simon Harvey" wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I know
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID may
> be null.
> Now, I need to Select all the books, including those that don't have an author.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books that
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a sneaking
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grateful
> Kindest Regards
> Simon
>
>

a TSQL question

i want to write a query that will return last created 200 records + record
with the minimum ID.
is there a way to do that in one single Select statement?
Following statement should return the record that has the minimum ID too.
SELECT TOP 200 ID , CREATEDATE
FROM TBL_RECORDS
ORDER BY CREATEDATE DESC
ID column has IDENTITY property and There could be records with negative IDs
which come through synchronization.
Thanks in advance..Well, what do you mean by single select? Union would probably be the best
solution here, why dont you use that?
MC
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:uJjpdS$KGHA.2300@.TK2MSFTNGP15.phx.gbl...
>i want to write a query that will return last created 200 records + record
>with the minimum ID.
> is there a way to do that in one single Select statement?
> Following statement should return the record that has the minimum ID too.
> SELECT TOP 200 ID , CREATEDATE
> FROM TBL_RECORDS
> ORDER BY CREATEDATE DESC
> ID column has IDENTITY property and There could be records with negative
> IDs which come through synchronization.
> Thanks in advance..
>|||Thanks for the answers ,

> Well, what do you mean by single select? Union would probably be the best
> solution here, why dont you use that?
that is what i mean with single select! i plan to use this query in a RDA
application. I am
not sure that RDA's Pull method with "tracking on" option supports the
UNION operator.
Anyway , i will give a try with UNION method,i hope it works :)

A trick query.

Hi all...

I would like to know how it is possible to make my problem below all in ONE
query or stored procedure.

I select some rows from a table where the resultset is one column with some
values. Lets say 1, 4 and 7:

row val
1 1
2 4
3 7

Then I would like these results to manipulate another table together with
another value (lets say some 'b' with value 5)

Lets say the other table looks like this:

id a b text
1 1 1 'Some text'
2 1 3 'Some text'
3 1 5 'Some text'
4 4 5 'Some text'
5 7 4 'Some text'
6 2 5 'Some text'

in the above example the rows with id 3 and 4 match my criteria because 1
and 4 (and not 7) was in the column 'a' together with the value 5 in column
'b'.

Here comes the tricky part (at least for me):
Now because a row without the 'a' value 7 and the 'b' value 5 existed in the
table I would like to create one row with those values.
Also because the 'b' column did have a the value 5 (the row with id 6)
without any of the 'a' values of 1, 4 and 7 (here 'a' is 2), that row shall
be deleted.

Then at last I would like the resultset matching 'a' column of 1, 4 and 7
AND 'b' column 5 as a resultset.

I hope that it is understandable and someone can help.

- rick -So ,basically, you want to have all values from the table a and matching
values (if they exist) from table b? How does this work for you:

select
Table1.val,
5 as CriteriaValue, --probably variable...
OtherTable.id,
OtherTable.Text
from
Table1
left join OtherTable on table1.val = OtherTable.a and OtherTable.b = 5

Now, if this query works, you can delete all values from OtherTable that
have a value b = 5 and id not in the select. You can insert the rows
returned by query if you add filter WHERE otherTable.ID is null.
If this doesnt work for you, please elaborate...

MC

"Rick" <rickcool22@.hotmail.comwrote in message
news:45d71f80$0$90276$14726298@.news.sunsite.dk...

Quote:

Originally Posted by

Hi all...
>
I would like to know how it is possible to make my problem below all in
ONE query or stored procedure.
>
I select some rows from a table where the resultset is one column with
some values. Lets say 1, 4 and 7:
>
row val
1 1
2 4
3 7
>
Then I would like these results to manipulate another table together with
another value (lets say some 'b' with value 5)
>
Lets say the other table looks like this:
>
id a b text
1 1 1 'Some text'
2 1 3 'Some text'
3 1 5 'Some text'
4 4 5 'Some text'
5 7 4 'Some text'
6 2 5 'Some text'
>
in the above example the rows with id 3 and 4 match my criteria because 1
and 4 (and not 7) was in the column 'a' together with the value 5 in
column 'b'.
>
Here comes the tricky part (at least for me):
Now because a row without the 'a' value 7 and the 'b' value 5 existed in
the table I would like to create one row with those values.
Also because the 'b' column did have a the value 5 (the row with id 6)
without any of the 'a' values of 1, 4 and 7 (here 'a' is 2), that row
shall be deleted.
>
Then at last I would like the resultset matching 'a' column of 1, 4 and 7
AND 'b' column 5 as a resultset.
>
I hope that it is understandable and someone can help.
>
- rick -
>

A syntax error

Hi,

I write a test DMX as follows to expriment

cmd.CommandText = "SELECT FLATTENED " +
"( SELECT *, PredictStdev(BloodPressure) AS Stdev " +
"FROM PredictTimeSeries(BloodPressure, " + numTimePoints + ")" +
") " +
"FROM " + modelName;

However, after execute this command, a error show:

The syntax for 'Stdev' is incorrect. I have no idea about the reason to raise to error.

How can I fix it and why this error occur?

Any help would be welcome.

Ricky.

Stdev is a keyword in the language supported by our server. To use it as a column name, you need to use brackets:

cmd.CommandText = "SELECT FLATTENED " +
"( SELECT *, PredictStdev(BloodPressure) AS [Stdev] " +
"FROM PredictTimeSeries(BloodPressure, " + numTimePoints + ")" +
") " +
"FROM " + modelName;|||

Thx Bogdan.

Regards,

Ricky

a substitute for UNION?

I'd like to change query:
SELECT DM.*, 'condition1', NULL FROM DM
WHERE (condition1)
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE (condition2)
to one SELECT like this
SELECT DM.*, WasCondition1, WasCondition2 FROM DM
WHERE (condition1) or (condition2)
but how to fill in the WasConditionX column?
The UNION version was bad because in case a row fulfilled 2
conditions, it was repeated instead of joining them, such like this:
SELECT DM.*, 'condition1', 'condition2' FROM DM
WHERE condition1 AND condition2
UNION
SELECT DM.*, 'condition1', NULL FROM DM
WHERE condition1 AND NOT condition2
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE NOT condition1 AND condition2
or (say DM has columns A, B, C):
SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
(
SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
WHERE condition1
UNION
SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
WHERE condition2
) AS DM
GROUP BY A, B, COn 19 Feb, 20:46, bbl...@.op.pl wrote:
> I'd like to change query:
> SELECT DM.*, 'condition1', NULL FROM DM
> WHERE (condition1)
> UNION
> SELECT DM.*, NULL, 'condition2' FROM DM
> WHERE (condition2)
> to one SELECT like this
> SELECT DM.*, WasCondition1, WasCondition2 FROM DM
> WHERE (condition1) or (condition2)
> but how to fill in the WasConditionX column?
> The UNION version was bad because in case a row fulfilled 2
> conditions, it was repeated instead of joining them, such like this:
> SELECT DM.*, 'condition1', 'condition2' FROM DM
> WHERE condition1 AND condition2
> UNION
> SELECT DM.*, 'condition1', NULL FROM DM
> WHERE condition1 AND NOT condition2
> UNION
> SELECT DM.*, NULL, 'condition2' FROM DM
> WHERE NOT condition1 AND condition2
> or (say DM has columns A, B, C):
> SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
> (
> SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
> WHERE condition1
> UNION
> SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
> WHERE condition2
> ) AS DM
> GROUP BY A, B, C
Use a CASE expression. To adapt your second example:
SELECT col1, col2, col3,
CASE WHEN (condition1) THEN 'Y' ELSE 'N' END AS WasCondition1,
CASE WHEN (condition2) THEN 'Y' ELSE 'N' END AS WasCondition2
WHERE (condition1) OR (condition2) ;
Always avoid using SELECT *.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> Use a CASE expression. To adapt your second example:
> SELECT col1, col2, col3,
> CASE WHEN (condition1) THEN 'Y' ELSE 'N' END AS WasCondition1,
> CASE WHEN (condition2) THEN 'Y' ELSE 'N' END AS WasCondition2
> WHERE (condition1) OR (condition2) ;
Well, then each condition will be checked twice -- performance
decreases (the conditions use subqueries)?
> Always avoid using SELECT *.
Why?|||I'll show you my query which doesn't want to compile for some reason
(Incorrect syntax near the keyword GROUP):
DECLARE @.pp INT
SET @.pp = 7
SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
FROM (
SELECT 1 AS Sent, 0 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.SenderID = @.pp
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverPersonID = @.pp
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
AND DM.StationID IN
(SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
AND DM.InstructionNr IN
(SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @.pp AND I.PUID IN
(SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
)
)
GROUP BY [ID]|||You are missing naming the derived table. Note the AS tbl at the end of the inner query:
SELECT ...
FROM
(
SELECT ... FROM...
) AS tbl
WHERE ...
GROUP BY...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<bbla32@.op.pl> wrote in message news:1171920105.335960.271700@.m58g2000cwm.googlegroups.com...
> I'll show you my query which doesn't want to compile for some reason
> (Incorrect syntax near the keyword GROUP):
> DECLARE @.pp INT
> SET @.pp = 7
>
> SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
> FROM (
> SELECT 1 AS Sent, 0 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.SenderID = @.pp
> UNION
> SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.ReceiverPersonID = @.pp
> UNION
> SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
> AND DM.StationID IN
> (SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
> WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
> UNION
> SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
> AND DM.InstructionNr IN
> (SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
> WHERE I.PUID = @.pp AND I.PUID IN
> (SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
> )
> )
> GROUP BY [ID]
>|||On Feb 19, 10:31 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> You are missing naming the derived table. Note the AS tbl at the end of the inner query:
> SELECT ...
> FROM
> (
> SELECT ... FROM...
> ) AS tbl
> WHERE ...
> GROUP BY...
Thanks!
Can I write a faster query?|||bbla32@.op.pl wrote:
> On Feb 19, 10:31 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > You are missing naming the derived table. Note the AS tbl at the end of the inner query:
> >
> > SELECT ...
> > FROM
> > (
> > SELECT ... FROM...
> > ) AS tbl
> > WHERE ...
> > GROUP BY...
> Thanks!
> Can I write a faster query?
A few tips:
- Only select the columns that you need in the derived table. IOW,
change "DM.*" to "DM.ID"
- Make sure you have proper indexes in place. For example on
DocumentMessages(SenderID,ObsoleteDate) and on
DocumentMessages(ReceiverPersonID,ObsoleteDate)
- Remove unnecessary IN clauses
Gert-Jan|||Assuming I didn't make a mistake - the code is untested of course -
the following might perform slightly better. Or not, a lot depends on
the indexing and actual data.
SELECT SUM(Sent) AS Sent,
SUM(Received) AS Received,
[ID]
FROM (SELECT CASE WHEN DM.SenderID = @.pp
THEN 1
ELSE 0
END AS Sent,
CASE WHEN DM.ReceiverPersonID = @.pp
THEN 1
WHEN DM.ReceiverDepartmentID NOT IN
(SELECT PD.DepartmentID
FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp
AND PD.ObsoleteDate IS NULL)
THEN 0
WHEN DM.StationID IN
(SELECT PS.StationID
FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @.pp
AND PS.ObsoleteDate IS NULL)
THEN 1
WHEN DM.InstructionNr IN
(SELECT I.InstructionNr
FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @.pp
AND I.PUID IN
(SELECT PU.ID
FROM WSDMS..PUs PU))
THEN 1
ELSE 0
END AS Received,
DM.*
FROM WSDMS..DocumentMessages DM
WHERE DM.ObsoleteDate IS NULL) as X
GROUP BY [ID]
I left all the IN tests as IN tests, (though one was reversed to a NOT
IN). Another change that could be worth trying is to rewrite each one
as an EXISTS test. In any case the use of subqueries in the CASE
should limit the number of times the subqueries are executed, which is
where any performance gain will come from.
Roy Harvey
Beacon Falls, CT
On 19 Feb 2007 13:21:45 -0800, bbla32@.op.pl wrote:
>I'll show you my query which doesn't want to compile for some reason
>(Incorrect syntax near the keyword GROUP):
>DECLARE @.pp INT
>SET @.pp = 7
>
>SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
>FROM (
>SELECT 1 AS Sent, 0 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.SenderID = @.pp
>UNION
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverPersonID = @.pp
>UNION
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
>AND DM.StationID IN
> (SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
> WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
>UNION
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
>AND DM.InstructionNr IN
> (SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
> WHERE I.PUID = @.pp AND I.PUID IN
> (SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
> )
>)
>GROUP BY [ID]|||On Feb 19, 11:33 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> Assuming I didn't make a mistake - the code is untested of course -
> the following might perform slightly better. Or not, a lot depends on
> the indexing and actual data.
Great example!
> as an EXISTS test. In any case the use of subqueries in the CASE
> should limit the number of times the subqueries are executed, which is
> where any performance gain will come from.
Well, wouldn't it execute slower since the subquery is within CASE?
I have indices only on ID columns for all tables.|||On 19 Feb 2007 15:01:46 -0800, bbla32@.op.pl wrote:
>On Feb 19, 11:33 pm, Roy Harvey <roy_har...@.snet.net> wrote:
>> as an EXISTS test. In any case the use of subqueries in the CASE
>> should limit the number of times the subqueries are executed, which is
>> where any performance gain will come from.
>Well, wouldn't it execute slower since the subquery is within CASE?
No reason why that should be an issue.
The best way to make the subqueries faster is to run them less. If
the first WHEN is satisfied the subqueries are not run at all. If the
first subquery matches, the second subquery is not run at all. In the
original query every subquery was run against every row in the table -
and every row was processed as many times as there were UNIONed
SELECTs. So the idea behind moving them into the CASE is to run them
once per incoming row, and then as few of them as is required.
Roy Harvey
Beacon Falls, CT|||> The best way to make the subqueries faster is to run them less. If
> the first WHEN is satisfied the subqueries are not run at all. If the
> first subquery matches, the second subquery is not run at all. In the
> original query every subquery was run against every row in the table -
> and every row was processed as many times as there were UNIONed
> SELECTs. So the idea behind moving them into the CASE is to run them
> once per incoming row, and then as few of them as is required.
That makes sense. I have a problem with your query though: in the
construct like below
SELECT CASE
WHEN DM.ReceiverDepartmentID NOT IN (1)
THEN 0
ELSE 1
END
when DM.ReceiverDepartmentID is null, it returns 1 instead of 0?|||> when DM.ReceiverDepartmentID is null, it returns 1 instead of 0?
I changed to
WHEN DM.ReceiverDepartmentID IS NULL OR DM.ReceiverDepartmentID NOT
IN ...
and
WHEN DM.StationID IS NOT NULL AND DM.StationID IN ...
I suppose it should be ok now.|||On 19 Feb 2007 15:53:06 -0800, bbla32@.op.pl wrote:
>> when DM.ReceiverDepartmentID is null, it returns 1 instead of 0?
>
>I changed to
> WHEN DM.ReceiverDepartmentID IS NULL OR DM.ReceiverDepartmentID NOT
>IN ...
>and
> WHEN DM.StationID IS NOT NULL AND DM.StationID IN ...
>I suppose it should be ok now.
That is the problem with not haveing the real table definitions and
test data to work with. Your correction is fine.
Roy Harvey
Beacon Falls, CT

A stored procedure

Hello!
Could anyone help me with this stored procedure...!?
Table Cars:
-Id
-Model
-Make
-Year
...
Table PriceList
-Id
-CarId
-Price
....
I would like to select all fields from "Cars" and only MIN(Price) from
"PriceList" WHERE Cars.Id = PriceList.CarId. and join them into a single
result set.
Thanks!
James
Hello,
Try with
SELECT id, Model, Make, Year, (SELECT MIN(Price) FROM PriceList WHERE CarId
= c.Id)
FROM Cars c
Regards,
Tomislav Kralj
"James T." <gimenei@.hotmail.com> wrote in message
news:uSyVIOUKFHA.572@.tk2msftngp13.phx.gbl...
> Hello!
> Could anyone help me with this stored procedure...!?
> Table Cars:
> -Id
> -Model
> -Make
> -Year
> ...
> Table PriceList
> -Id
> -CarId
> -Price
> ...
> I would like to select all fields from "Cars" and only MIN(Price) from
> "PriceList" WHERE Cars.Id = PriceList.CarId. and join them into a single
> result set.
> Thanks!
> James
>

A stored procedure

Hello!
Could anyone help me with this stored procedure...!?
Table Cars:
-Id
-Model
-Make
-Year
...
Table PriceList
-Id
-CarId
-Price
...
I would like to select all fields from "Cars" and only MIN(Price) from
"PriceList" WHERE Cars.Id = PriceList.CarId. and join them into a single
result set.
Thanks!
JamesJames
SELECT <column lists> FROM Cars JOIN
(
SELECT MIN(Price) Price,CarId FROM PriceList
GROUP BY CarId
) AS Der
ON Cars.CarId =Der.CarId
"James T." <gimenei@.hotmail.com> wrote in message
news:uNaS$NUKFHA.4092@.tk2msftngp13.phx.gbl...
> Hello!
> Could anyone help me with this stored procedure...!?
> Table Cars:
> -Id
> -Model
> -Make
> -Year
> ...
> Table PriceList
> -Id
> -CarId
> -Price
> ...
> I would like to select all fields from "Cars" and only MIN(Price) from
> "PriceList" WHERE Cars.Id = PriceList.CarId. and join them into a single
> result set.
> Thanks!
> James
>

A stored procedure

Hello!
Could anyone help me with this stored procedure...!?
Table Cars:
-Id
-Model
-Make
-Year
...
Table PriceList
-Id
-CarId
-Price
...
I would like to select all fields from "Cars" and only MIN(Price) from
"PriceList" WHERE Cars.Id = PriceList.CarId. and join them into a single
result set.
Thanks!
JamesHello,
Try with
SELECT id, Model, Make, Year, (SELECT MIN(Price) FROM PriceList WHERE CarId
= c.Id)
FROM Cars c
Regards,
Tomislav Kralj
"James T." <gimenei@.hotmail.com> wrote in message
news:uSyVIOUKFHA.572@.tk2msftngp13.phx.gbl...
> Hello!
> Could anyone help me with this stored procedure...!?
> Table Cars:
> -Id
> -Model
> -Make
> -Year
> ...
> Table PriceList
> -Id
> -CarId
> -Price
> ...
> I would like to select all fields from "Cars" and only MIN(Price) from
> "PriceList" WHERE Cars.Id = PriceList.CarId. and join them into a single
> result set.
> Thanks!
> James
>

A statement that returns what is and what is not in the database

Hi!!
I wonder if anyone can help. I have a select statement :e.g
select * from products
where productref in ('123456',
'123457',
'123458',
'123459'
and so on)
The products that are in the database come are returned as results.
So,
The query returns:
123456 - sadjsjfsdfjlksf
123457',- fdfslkdjfdj
123458',- dfsjflkdjlsjf
But I also need to find out what is not returned. There are about
10,000 lines and I need to find out what has not been returned. please
help
select * from products
where productref NOT in ('123456',
'123457',
'123458',
'123459'
and so on)
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
"Himani" <himani77@.gmail.com> wrote in message
news:1171103166.734403.217840@.s48g2000cws.googlegr oups.com...
> Hi!!
> I wonder if anyone can help. I have a select statement :e.g
> select * from products
> where productref in ('123456',
> '123457',
> '123458',
> '123459'
> and so on)
> The products that are in the database come are returned as results.
> So,
> The query returns:
> 123456 - sadjsjfsdfjlksf
> 123457',- fdfslkdjfdj
> 123458',- dfsjflkdjlsjf
> But I also need to find out what is not returned. There are about
> 10,000 lines and I need to find out what has not been returned. please
> help
>
|||On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> select * from products
> where productref NOT in ('123456',
> '123457',
> '123458',
> '123459'
> and so on)
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171103166.734403.217840@.s48g2000cws.googlegr oups.com...
>
>
>
>
> - Show quoted text -
I did try this but it did not work. the query returned other products.
perhaps I can explain a bit more.
The key thing is that some of the products are not on the database at
all.The List
'123456',
123457',
'123458',
'123459'
is from another system in which all the products are there. Whilst the
select query returns
the top 4 products as they are in the database. I want another query
that returns the ones that are not in the database at all - i.e. it
should return 123459 etc but it doesn't.
|||> I want another query
> that returns the ones that are not in the database at all - i.e. it
> should return 123459 etc but it doesn't.
One method:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '123456' AS productref
UNION ALL SELECT '123457'
UNION ALL SELECT '123458'
UNION ALL SELECT '123459'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171128176.411862.307420@.a75g2000cwd.googlegr oups.com...
> On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> I did try this but it did not work. the query returned other products.
> perhaps I can explain a bit more.
> The key thing is that some of the products are not on the database at
> all.The List
> '123456',
> 123457',
> '123458',
> '123459'
> is from another system in which all the products are there. Whilst the
> select query returns
> the top 4 products as they are in the database. I want another query
> that returns the ones that are not in the database at all - i.e. it
> should return 123459 etc but it doesn't.
>
|||On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> One method:
> SELECT
> OtherDatabaseList.productref
> FROM
> (SELECT '123456' AS productref
> UNION ALL SELECT '123457'
> UNION ALL SELECT '123458'
> UNION ALL SELECT '123459'
> ) OtherDatabaseList
> WHERE NOT EXISTS
> (
> SELECT *
> FROM dbo.products p
> WHERE p.productref = OtherDatabaseList.productref
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171128176.411862.307420@.a75g2000cwd.googlegr oups.com...
>
>
>
>
>
>
>
>
> - Show quoted text -
hi !!
Thanks a lot for sql code. I understand the query but there is a
slight problem. I do not have the access to the other database. I
have been given a list of product refs between the range
114203-124675. Now this was an upload that wasn't done properly. So,
roughly 5241 products are on the system and 5233 are not. The products
that the database contains are easily obtainable by the following
query:
select * from products where productref in (114203,114204,114205 etc)
So this query returns 114203,114204 as they are in the database. But I
need to know is there a way of retrieving 114205 which is not on the
database. Any tips would be much appreciated.
Regards
Himani
|||> Thanks a lot for sql code. I understand the query but there is a
> slight problem. I do not have the access to the other database. I
> have been given a list of product refs between the range
> 114203-124675. Now this was an upload that wasn't done properly. So,
> roughly 5241 products are on the system and 5233 are not. The products
> that the database contains are easily obtainable by the following
> query:
> select * from products where productref in (114203,114204,114205 etc)
> So this query returns 114203,114204 as they are in the database. But I
> need to know is there a way of retrieving 114205 which is not on the
> database. Any tips would be much appreciated.
So you need to identify the products that are in the list but not in the
database, right? I believe the query I originally posted will do that -
just specify the actual list of all products in the list you were given as
the derrived table. For example, I would expect the query below to return
only 114205 if 11403 and 11404 are in the products table.
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171190077.447791.188360@.q2g2000cwa.googlegro ups.com...
> On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
> hi !!
> Thanks a lot for sql code. I understand the query but there is a
> slight problem. I do not have the access to the other database. I
> have been given a list of product refs between the range
> 114203-124675. Now this was an upload that wasn't done properly. So,
> roughly 5241 products are on the system and 5233 are not. The products
> that the database contains are easily obtainable by the following
> query:
> select * from products where productref in (114203,114204,114205 etc)
> So this query returns 114203,114204 as they are in the database. But I
> need to know is there a way of retrieving 114205 which is not on the
> database. Any tips would be much appreciated.
> Regards
> Himani
>
|||On 11 Feb, 15:25, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
>
> So you need to identify the products that are in the list but not in the
> database, right? I believe the query I originally posted will do that -
> just specify the actual list of all products in the list you were given as
> the derrived table. For example, I would expect the query below to return
> only 114205 if 11403 and 11404 are in the products table.
> SELECT
> OtherDatabaseList.productref
> FROM
> (SELECT '114203' AS productref
> UNION ALL SELECT '114204'
> UNION ALL SELECT '114205'
> ) OtherDatabaseList
> WHERE NOT EXISTS
> (
> SELECT *
> FROM dbo.products p
> WHERE p.productref = OtherDatabaseList.productref
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171190077.447791.188360@.q2g2000cwa.googlegro ups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Many Thanks. It did work. Just wondering if you could just explain the
scrip a bit if you had some time or comment it.
Regards
Himani
|||> Many Thanks. It did work. Just wondering if you could just explain the
> scrip a bit if you had some time or comment it.
There are a few different techniques to get the desired list but all require
that you have a table that contains the complete list of products that
should be in the database. You could either create and load a table with
those products or use the derived table (inline view). I chose a derived
table because this was one-time query.
To expand derived tables, start with a query that returns the desired
result:
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
Then enclose it in parenthesis and specify an alias:
(
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) AS OtherDatabaseList
You can then use it anywhere in a query where a normal table can be
specified:
SELECT *
FROM
(
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) AS OtherDatabaseList
Now that you have the list of what should be in the database, you can
identify the rows that are not in the target table using one of several
techniques:
NOT EXISTS:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
NOT IN:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE OtherDatabaseList.productref NOT IN
(
SELECT p.productref
FROM dbo.products p
)
Note that the NOT IN method is dangerous for nullable columns; no rows will
be returned if any NULL values are in the NOT IN list. I always use NOT
EXISTS instead to avoid that issue:
OUTER JOIN:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
LEFT OUTER JOIN dbo.products p ON
p.productref = OtherDatabaseList.productref
WHERE p.productref IS NULL
MINUS (SQL 2005):
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
EXCEPT
SELECT p.productref FROM dbo.products p
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171296448.577247.172070@.l53g2000cwa.googlegr oups.com...
> On 11 Feb, 15:25, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
> Many Thanks. It did work. Just wondering if you could just explain the
> scrip a bit if you had some time or comment it.
> Regards
> Himani
>

A statement that returns what is and what is not in the database

Hi!!
I wonder if anyone can help. I have a select statement :e.g
select * from products
where productref in ('123456',
'123457',
'123458',
'123459'
and so on)
The products that are in the database come are returned as results.
So,
The query returns:
123456 - sadjsjfsdfjlksf
123457',- fdfslkdjfdj
123458',- dfsjflkdjlsjf
But I also need to find out what is not returned. There are about
10,000 lines and I need to find out what has not been returned. please
helpselect * from products
where productref NOT in ('123456',
'123457',
'123458',
'123459'
and so on)
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
"Himani" <himani77@.gmail.com> wrote in message
news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
> Hi!!
> I wonder if anyone can help. I have a select statement :e.g
> select * from products
> where productref in ('123456',
> '123457',
> '123458',
> '123459'
> and so on)
> The products that are in the database come are returned as results.
> So,
> The query returns:
> 123456 - sadjsjfsdfjlksf
> 123457',- fdfslkdjfdj
> 123458',- dfsjflkdjlsjf
> But I also need to find out what is not returned. There are about
> 10,000 lines and I need to find out what has not been returned. please
> help
>|||On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> select * from products
> where productref NOT in ('123456',
> '123457',
> '123458',
> '123459'
> and so on)
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
>
> > Hi!!
> > I wonder if anyone can help. I have a select statement :e.g
> > select * from products
> > where productref in ('123456',
> > '123457',
> > '123458',
> > '123459'
> > and so on)
> > The products that are in the database come are returned as results.
> > So,
> > The query returns:
> > 123456 - sadjsjfsdfjlksf
> > 123457',- fdfslkdjfdj
> > 123458',- dfsjflkdjlsjf
> > But I also need to find out what is not returned. There are about
> > 10,000 lines and I need to find out what has not been returned. please
> > help- Hide quoted text -
> - Show quoted text -
I did try this but it did not work. the query returned other products.
perhaps I can explain a bit more.
The key thing is that some of the products are not on the database at
all.The List
'123456',
123457',
'123458',
'123459'
is from another system in which all the products are there. Whilst the
select query returns
the top 4 products as they are in the database. I want another query
that returns the ones that are not in the database at all - i.e. it
should return 123459 etc but it doesn't.|||> I want another query
> that returns the ones that are not in the database at all - i.e. it
> should return 123459 etc but it doesn't.
One method:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '123456' AS productref
UNION ALL SELECT '123457'
UNION ALL SELECT '123458'
UNION ALL SELECT '123459'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
> On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>> select * from products
>> where productref NOT in ('123456',
>> '123457',
>> '123458',
>> '123459'
>> and so on)
>> --
>> Hilary Cotter
>> Looking for a SQL Server replication
>> book?http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL
>> FTShttp://www.indexserverfaq.com
>> "Himani" <himan...@.gmail.com> wrote in message
>> news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
>>
>> > Hi!!
>> > I wonder if anyone can help. I have a select statement :e.g
>> > select * from products
>> > where productref in ('123456',
>> > '123457',
>> > '123458',
>> > '123459'
>> > and so on)
>> > The products that are in the database come are returned as results.
>> > So,
>> > The query returns:
>> > 123456 - sadjsjfsdfjlksf
>> > 123457',- fdfslkdjfdj
>> > 123458',- dfsjflkdjlsjf
>> > But I also need to find out what is not returned. There are about
>> > 10,000 lines and I need to find out what has not been returned. please
>> > help- Hide quoted text -
>> - Show quoted text -
> I did try this but it did not work. the query returned other products.
> perhaps I can explain a bit more.
> The key thing is that some of the products are not on the database at
> all.The List
> '123456',
> 123457',
> '123458',
> '123459'
> is from another system in which all the products are there. Whilst the
> select query returns
> the top 4 products as they are in the database. I want another query
> that returns the ones that are not in the database at all - i.e. it
> should return 123459 etc but it doesn't.
>|||On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > I want another query
> > that returns the ones that are not in the database at all - i.e. it
> > should return 123459 etc but it doesn't.
> One method:
> SELECT
> OtherDatabaseList.productref
> FROM
> (SELECT '123456' AS productref
> UNION ALL SELECT '123457'
> UNION ALL SELECT '123458'
> UNION ALL SELECT '123459'
> ) OtherDatabaseList
> WHERE NOT EXISTS
> (
> SELECT *
> FROM dbo.products p
> WHERE p.productref = OtherDatabaseList.productref
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
>
> > On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> >> select * from products
> >> where productref NOT in ('123456',
> >> '123457',
> >> '123458',
> >> '123459'
> >> and so on)
> >> --
> >> Hilary Cotter
> >> Looking for a SQL Server replication
> >> book?http://www.nwsu.com/0974973602.html
> >> Looking for a FAQ on Indexing Services/SQL
> >> FTShttp://www.indexserverfaq.com
> >> "Himani" <himan...@.gmail.com> wrote in message
> >>news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
> >> > Hi!!
> >> > I wonder if anyone can help. I have a select statement :e.g
> >> > select * from products
> >> > where productref in ('123456',
> >> > '123457',
> >> > '123458',
> >> > '123459'
> >> > and so on)
> >> > The products that are in the database come are returned as results.
> >> > So,
> >> > The query returns:
> >> > 123456 - sadjsjfsdfjlksf
> >> > 123457',- fdfslkdjfdj
> >> > 123458',- dfsjflkdjlsjf
> >> > But I also need to find out what is not returned. There are about
> >> > 10,000 lines and I need to find out what has not been returned. please
> >> > help- Hide quoted text -
> >> - Show quoted text -
> > I did try this but it did not work. the query returned other products.
> > perhaps I can explain a bit more.
> > The key thing is that some of the products are not on the database at
> > all.The List
> > '123456',
> > 123457',
> > '123458',
> > '123459'
> > is from another system in which all the products are there. Whilst the
> > select query returns
> > the top 4 products as they are in the database. I want another query
> > that returns the ones that are not in the database at all - i.e. it
> > should return 123459 etc but it doesn't.- Hide quoted text -
> - Show quoted text -
hi !!
Thanks a lot for sql code. I understand the query but there is a
slight problem. I do not have the access to the other database. I
have been given a list of product refs between the range
114203-124675. Now this was an upload that wasn't done properly. So,
roughly 5241 products are on the system and 5233 are not. The products
that the database contains are easily obtainable by the following
query:
select * from products where productref in (114203,114204,114205 etc)
So this query returns 114203,114204 as they are in the database. But I
need to know is there a way of retrieving 114205 which is not on the
database. Any tips would be much appreciated.
Regards
Himani|||> Thanks a lot for sql code. I understand the query but there is a
> slight problem. I do not have the access to the other database. I
> have been given a list of product refs between the range
> 114203-124675. Now this was an upload that wasn't done properly. So,
> roughly 5241 products are on the system and 5233 are not. The products
> that the database contains are easily obtainable by the following
> query:
> select * from products where productref in (114203,114204,114205 etc)
> So this query returns 114203,114204 as they are in the database. But I
> need to know is there a way of retrieving 114205 which is not on the
> database. Any tips would be much appreciated.
So you need to identify the products that are in the list but not in the
database, right? I believe the query I originally posted will do that -
just specify the actual list of all products in the list you were given as
the derrived table. For example, I would expect the query below to return
only 114205 if 11403 and 11404 are in the products table.
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171190077.447791.188360@.q2g2000cwa.googlegroups.com...
> On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>> > I want another query
>> > that returns the ones that are not in the database at all - i.e. it
>> > should return 123459 etc but it doesn't.
>> One method:
>> SELECT
>> OtherDatabaseList.productref
>> FROM
>> (SELECT '123456' AS productref
>> UNION ALL SELECT '123457'
>> UNION ALL SELECT '123458'
>> UNION ALL SELECT '123459'
>> ) OtherDatabaseList
>> WHERE NOT EXISTS
>> (
>> SELECT *
>> FROM dbo.products p
>> WHERE p.productref = OtherDatabaseList.productref
>> )
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Himani" <himan...@.gmail.com> wrote in message
>> news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
>>
>> > On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>> >> select * from products
>> >> where productref NOT in ('123456',
>> >> '123457',
>> >> '123458',
>> >> '123459'
>> >> and so on)
>> >> --
>> >> Hilary Cotter
>> >> Looking for a SQL Server replication
>> >> book?http://www.nwsu.com/0974973602.html
>> >> Looking for a FAQ on Indexing Services/SQL
>> >> FTShttp://www.indexserverfaq.com
>> >> "Himani" <himan...@.gmail.com> wrote in message
>> >>news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
>> >> > Hi!!
>> >> > I wonder if anyone can help. I have a select statement :e.g
>> >> > select * from products
>> >> > where productref in ('123456',
>> >> > '123457',
>> >> > '123458',
>> >> > '123459'
>> >> > and so on)
>> >> > The products that are in the database come are returned as results.
>> >> > So,
>> >> > The query returns:
>> >> > 123456 - sadjsjfsdfjlksf
>> >> > 123457',- fdfslkdjfdj
>> >> > 123458',- dfsjflkdjlsjf
>> >> > But I also need to find out what is not returned. There are about
>> >> > 10,000 lines and I need to find out what has not been returned.
>> >> > please
>> >> > help- Hide quoted text -
>> >> - Show quoted text -
>> > I did try this but it did not work. the query returned other products.
>> > perhaps I can explain a bit more.
>> > The key thing is that some of the products are not on the database at
>> > all.The List
>> > '123456',
>> > 123457',
>> > '123458',
>> > '123459'
>> > is from another system in which all the products are there. Whilst the
>> > select query returns
>> > the top 4 products as they are in the database. I want another query
>> > that returns the ones that are not in the database at all - i.e. it
>> > should return 123459 etc but it doesn't.- Hide quoted text -
>> - Show quoted text -
> hi !!
> Thanks a lot for sql code. I understand the query but there is a
> slight problem. I do not have the access to the other database. I
> have been given a list of product refs between the range
> 114203-124675. Now this was an upload that wasn't done properly. So,
> roughly 5241 products are on the system and 5233 are not. The products
> that the database contains are easily obtainable by the following
> query:
> select * from products where productref in (114203,114204,114205 etc)
> So this query returns 114203,114204 as they are in the database. But I
> need to know is there a way of retrieving 114205 which is not on the
> database. Any tips would be much appreciated.
> Regards
> Himani
>|||On 11 Feb, 15:25, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > Thanks a lot for sql code. I understand the query but there is a
> > slight problem. I do not have the access to the other database. I
> > have been given a list of product refs between the range
> > 114203-124675. Now this was an upload that wasn't done properly. So,
> > roughly 5241 products are on the system and 5233 are not. The products
> > that the database contains are easily obtainable by the following
> > query:
> > select * from products where productref in (114203,114204,114205 etc)
> > So this query returns 114203,114204 as they are in the database. But I
> > need to know is there a way of retrieving 114205 which is not on the
> > database. Any tips would be much appreciated.
> So you need to identify the products that are in the list but not in the
> database, right? I believe the query I originally posted will do that -
> just specify the actual list of all products in the list you were given as
> the derrived table. For example, I would expect the query below to return
> only 114205 if 11403 and 11404 are in the products table.
> SELECT
> OtherDatabaseList.productref
> FROM
> (SELECT '114203' AS productref
> UNION ALL SELECT '114204'
> UNION ALL SELECT '114205'
> ) OtherDatabaseList
> WHERE NOT EXISTS
> (
> SELECT *
> FROM dbo.products p
> WHERE p.productref = OtherDatabaseList.productref
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171190077.447791.188360@.q2g2000cwa.googlegroups.com...
>
> > On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> > wrote:
> >> > I want another query
> >> > that returns the ones that are not in the database at all - i.e. it
> >> > should return 123459 etc but it doesn't.
> >> One method:
> >> SELECT
> >> OtherDatabaseList.productref
> >> FROM
> >> (SELECT '123456' AS productref
> >> UNION ALL SELECT '123457'
> >> UNION ALL SELECT '123458'
> >> UNION ALL SELECT '123459'
> >> ) OtherDatabaseList
> >> WHERE NOT EXISTS
> >> (
> >> SELECT *
> >> FROM dbo.products p
> >> WHERE p.productref = OtherDatabaseList.productref
> >> )
> >> --
> >> Hope this helps.
> >> Dan Guzman
> >> SQL Server MVP
> >> "Himani" <himan...@.gmail.com> wrote in message
> >>news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
> >> > On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> >> >> select * from products
> >> >> where productref NOT in ('123456',
> >> >> '123457',
> >> >> '123458',
> >> >> '123459'
> >> >> and so on)
> >> >> --
> >> >> Hilary Cotter
> >> >> Looking for a SQL Server replication
> >> >> book?http://www.nwsu.com/0974973602.html
> >> >> Looking for a FAQ on Indexing Services/SQL
> >> >> FTShttp://www.indexserverfaq.com
> >> >> "Himani" <himan...@.gmail.com> wrote in message
> >> >>news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
> >> >> > Hi!!
> >> >> > I wonder if anyone can help. I have a select statement :e.g
> >> >> > select * from products
> >> >> > where productref in ('123456',
> >> >> > '123457',
> >> >> > '123458',
> >> >> > '123459'
> >> >> > and so on)
> >> >> > The products that are in the database come are returned as results.
> >> >> > So,
> >> >> > The query returns:
> >> >> > 123456 - sadjsjfsdfjlksf
> >> >> > 123457',- fdfslkdjfdj
> >> >> > 123458',- dfsjflkdjlsjf
> >> >> > But I also need to find out what is not returned. There are about
> >> >> > 10,000 lines and I need to find out what has not been returned.
> >> >> > please
> >> >> > help- Hide quoted text -
> >> >> - Show quoted text -
> >> > I did try this but it did not work. the query returned other products.
> >> > perhaps I can explain a bit more.
> >> > The key thing is that some of the products are not on the database at
> >> > all.The List
> >> > '123456',
> >> > 123457',
> >> > '123458',
> >> > '123459'
> >> > is from another system in which all the products are there. Whilst the
> >> > select query returns
> >> > the top 4 products as they are in the database. I want another query
> >> > that returns the ones that are not in the database at all - i.e. it
> >> > should return 123459 etc but it doesn't.- Hide quoted text -
> >> - Show quoted text -
> > hi !!
> > Thanks a lot for sql code. I understand the query but there is a
> > slight problem. I do not have the access to the other database. I
> > have been given a list of product refs between the range
> > 114203-124675. Now this was an upload that wasn't done properly. So,
> > roughly 5241 products are on the system and 5233 are not. The products
> > that the database contains are easily obtainable by the following
> > query:
> > select * from products where productref in (114203,114204,114205 etc)
> > So this query returns 114203,114204 as they are in the database. But I
> > need to know is there a way of retrieving 114205 which is not on the
> > database. Any tips would be much appreciated.
> > Regards
> > Himani- Hide quoted text -
> - Show quoted text -
Many Thanks. It did work. Just wondering if you could just explain the
scrip a bit if you had some time or comment it.
Regards
Himani|||> Many Thanks. It did work. Just wondering if you could just explain the
> scrip a bit if you had some time or comment it.
There are a few different techniques to get the desired list but all require
that you have a table that contains the complete list of products that
should be in the database. You could either create and load a table with
those products or use the derived table (inline view). I chose a derived
table because this was one-time query.
To expand derived tables, start with a query that returns the desired
result:
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
Then enclose it in parenthesis and specify an alias:
(
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) AS OtherDatabaseList
You can then use it anywhere in a query where a normal table can be
specified:
SELECT *
FROM
(
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) AS OtherDatabaseList
Now that you have the list of what should be in the database, you can
identify the rows that are not in the target table using one of several
techniques:
NOT EXISTS:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
NOT IN:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE OtherDatabaseList.productref NOT IN
(
SELECT p.productref
FROM dbo.products p
)
Note that the NOT IN method is dangerous for nullable columns; no rows will
be returned if any NULL values are in the NOT IN list. I always use NOT
EXISTS instead to avoid that issue:
OUTER JOIN:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
LEFT OUTER JOIN dbo.products p ON
p.productref = OtherDatabaseList.productref
WHERE p.productref IS NULL
MINUS (SQL 2005):
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
EXCEPT
SELECT p.productref FROM dbo.products p
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171296448.577247.172070@.l53g2000cwa.googlegroups.com...
> On 11 Feb, 15:25, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>> > Thanks a lot for sql code. I understand the query but there is a
>> > slight problem. I do not have the access to the other database. I
>> > have been given a list of product refs between the range
>> > 114203-124675. Now this was an upload that wasn't done properly. So,
>> > roughly 5241 products are on the system and 5233 are not. The products
>> > that the database contains are easily obtainable by the following
>> > query:
>> > select * from products where productref in (114203,114204,114205 etc)
>> > So this query returns 114203,114204 as they are in the database. But I
>> > need to know is there a way of retrieving 114205 which is not on the
>> > database. Any tips would be much appreciated.
>> So you need to identify the products that are in the list but not in the
>> database, right? I believe the query I originally posted will do that -
>> just specify the actual list of all products in the list you were given
>> as
>> the derrived table. For example, I would expect the query below to
>> return
>> only 114205 if 11403 and 11404 are in the products table.
>> SELECT
>> OtherDatabaseList.productref
>> FROM
>> (SELECT '114203' AS productref
>> UNION ALL SELECT '114204'
>> UNION ALL SELECT '114205'
>> ) OtherDatabaseList
>> WHERE NOT EXISTS
>> (
>> SELECT *
>> FROM dbo.products p
>> WHERE p.productref = OtherDatabaseList.productref
>> )
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Himani" <himan...@.gmail.com> wrote in message
>> news:1171190077.447791.188360@.q2g2000cwa.googlegroups.com...
>>
>> > On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
>> > wrote:
>> >> > I want another query
>> >> > that returns the ones that are not in the database at all - i.e. it
>> >> > should return 123459 etc but it doesn't.
>> >> One method:
>> >> SELECT
>> >> OtherDatabaseList.productref
>> >> FROM
>> >> (SELECT '123456' AS productref
>> >> UNION ALL SELECT '123457'
>> >> UNION ALL SELECT '123458'
>> >> UNION ALL SELECT '123459'
>> >> ) OtherDatabaseList
>> >> WHERE NOT EXISTS
>> >> (
>> >> SELECT *
>> >> FROM dbo.products p
>> >> WHERE p.productref = OtherDatabaseList.productref
>> >> )
>> >> --
>> >> Hope this helps.
>> >> Dan Guzman
>> >> SQL Server MVP
>> >> "Himani" <himan...@.gmail.com> wrote in message
>> >>news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
>> >> > On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>> >> >> select * from products
>> >> >> where productref NOT in ('123456',
>> >> >> '123457',
>> >> >> '123458',
>> >> >> '123459'
>> >> >> and so on)
>> >> >> --
>> >> >> Hilary Cotter
>> >> >> Looking for a SQL Server replication
>> >> >> book?http://www.nwsu.com/0974973602.html
>> >> >> Looking for a FAQ on Indexing Services/SQL
>> >> >> FTShttp://www.indexserverfaq.com
>> >> >> "Himani" <himan...@.gmail.com> wrote in message
>> >> >>news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
>> >> >> > Hi!!
>> >> >> > I wonder if anyone can help. I have a select statement :e.g
>> >> >> > select * from products
>> >> >> > where productref in ('123456',
>> >> >> > '123457',
>> >> >> > '123458',
>> >> >> > '123459'
>> >> >> > and so on)
>> >> >> > The products that are in the database come are returned as
>> >> >> > results.
>> >> >> > So,
>> >> >> > The query returns:
>> >> >> > 123456 - sadjsjfsdfjlksf
>> >> >> > 123457',- fdfslkdjfdj
>> >> >> > 123458',- dfsjflkdjlsjf
>> >> >> > But I also need to find out what is not returned. There are about
>> >> >> > 10,000 lines and I need to find out what has not been returned.
>> >> >> > please
>> >> >> > help- Hide quoted text -
>> >> >> - Show quoted text -
>> >> > I did try this but it did not work. the query returned other
>> >> > products.
>> >> > perhaps I can explain a bit more.
>> >> > The key thing is that some of the products are not on the database
>> >> > at
>> >> > all.The List
>> >> > '123456',
>> >> > 123457',
>> >> > '123458',
>> >> > '123459'
>> >> > is from another system in which all the products are there. Whilst
>> >> > the
>> >> > select query returns
>> >> > the top 4 products as they are in the database. I want another query
>> >> > that returns the ones that are not in the database at all - i.e. it
>> >> > should return 123459 etc but it doesn't.- Hide quoted text -
>> >> - Show quoted text -
>> > hi !!
>> > Thanks a lot for sql code. I understand the query but there is a
>> > slight problem. I do not have the access to the other database. I
>> > have been given a list of product refs between the range
>> > 114203-124675. Now this was an upload that wasn't done properly. So,
>> > roughly 5241 products are on the system and 5233 are not. The products
>> > that the database contains are easily obtainable by the following
>> > query:
>> > select * from products where productref in (114203,114204,114205 etc)
>> > So this query returns 114203,114204 as they are in the database. But I
>> > need to know is there a way of retrieving 114205 which is not on the
>> > database. Any tips would be much appreciated.
>> > Regards
>> > Himani- Hide quoted text -
>> - Show quoted text -
> Many Thanks. It did work. Just wondering if you could just explain the
> scrip a bit if you had some time or comment it.
> Regards
> Himani
>