Is it possible to make "proxy tables" win SQL-server 2000?

Hello
I am coming from Sybase and are learning SQL-Server now.
In Sybase there is a feature called "proxy table" which makes it possible to create a proxy table that really exists in another database. But it appears a ordinary local table to a user. Does a similar feature exists in SQL-server? Anyone knows?
Thanks
Per
Per,
No, but you can fully qualify the object in the other database to access
it. i.e.
select * from database2.dbo.objectname
You will need the appropriate permissions on the other object.
You may also wish to look at cross-database ownership chaining in the
updated Books online available from www.microsoft.com/sql
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Per wrote:
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible to create a proxy table that really exists in another database. But it appears a ordinary local table to a user. Does a similar feature exists in SQL-server? Anyone knows?
> Thanks
> Per
|||Hi
To add to Marks post...you can also create a view in the "current" database.
That would mean that the three part name is only require in the view
definition.
John
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:CD9FFDA6-BCE1-4D83-8960-1D612462424D@.microsoft.com...
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it
appears a ordinary local table to a user. Does a similar feature exists in
SQL-server? Anyone knows?
> Thanks
> Per

Is it possible to make "proxy tables" win SQL-server 2000?

Hello
I am coming from Sybase and are learning SQL-Server now.
In Sybase there is a feature called "proxy table" which makes it possible to
create a proxy table that really exists in another database. But it appears
a ordinary local table to a user. Does a similar feature exists in SQL-serv
er? Anyone knows?
Thanks
PerPer,
No, but you can fully qualify the object in the other database to access
it. i.e.
select * from database2.dbo.objectname
You will need the appropriate permissions on the other object.
You may also wish to look at cross-database ownership chaining in the
updated Books online available from www.microsoft.com/sql
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Per wrote:
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it appea
rs a ordinary local table to a user. Does a similar feature exists in SQL-se
rver? Anyone knows?
> Thanks
> Per|||Hi
To add to Marks post...you can also create a view in the "current" database.
That would mean that the three part name is only require in the view
definition.
John
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:CD9FFDA6-BCE1-4D83-8960-1D612462424D@.microsoft.com...
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it
appears a ordinary local table to a user. Does a similar feature exists in
SQL-server? Anyone knows?
> Thanks
> Per

Is it possible to load such XML file using SQLXML BulkLoad?

Hello
I have an XML file:
<?xml version="1.0" encoding="UTF-8"?>
<root>
<Item>
<ItemID>1</ItemID>
<Property1>...</Property1>
<Property2>...</Property2>
<Property3>...</Property3>
</Item>
<Item>
<ItemID>2</ItemID>
<Property1>...</Property1>
<Property2>...</Property2>
<Property3>...</Property3>
<Property4>...</Property3>
..
</Item>
...
</root>
Number of properties for <Item> is not fixed and their names also not
defined (except ItemID). Is it possible to create XSD schema for
importing this into table using SQLXML BulkLoad facility?
Table:
ItemID PropertyName PropertyValue
=================================
1 Property1 ...
1 Property2 ...
1 Property3 ...
2 Property1 ...
2 Property2 ...
2 Property3 ...
2 Property4 ...
...
Thank you
Martin Rakhmanov
jimmers@.yandex.ruYou cannot map names of elements into data using the schema mapping. You
need to use OpenXML for such mappings.
HTH
Michael
"jimmers" <jimmers@.yandex.ru> wrote in message
news:b0ede647.0501200116.5025661d@.posting.google.com...
> Hello
> I have an XML file:
> <?xml version="1.0" encoding="UTF-8"?>
> <root>
> <Item>
> <ItemID>1</ItemID>
> <Property1>...</Property1>
> <Property2>...</Property2>
> <Property3>...</Property3>
> </Item>
> <Item>
> <ItemID>2</ItemID>
> <Property1>...</Property1>
> <Property2>...</Property2>
> <Property3>...</Property3>
> <Property4>...</Property3>
> ...
> </Item>
> ...
> </root>
> Number of properties for <Item> is not fixed and their names also not
> defined (except ItemID). Is it possible to create XSD schema for
> importing this into table using SQLXML BulkLoad facility?
> Table:
> ItemID PropertyName PropertyValue
> =================================
> 1 Property1 ...
> 1 Property2 ...
> 1 Property3 ...
> 2 Property1 ...
> 2 Property2 ...
> 2 Property3 ...
> 2 Property4 ...
> ...
>
> Thank you
> Martin Rakhmanov
> jimmers@.yandex.ru|||No, not this exact data file can be mapped using XSD schema. Use XSLT to
transform this data file to look something like, .
<?xml version="1.0" encoding="UTF-8"?>
<root>
<Item>
<ItemID>1</ItemID>
<Property><Name>Property1</Name><Value>...</Value></Property>
<Property><Name>Property2</Name><Value>...</Value></Property>
<Property><Name>Property3</Name><Value>...</Value></Property>
</Item>
HTH,
Chandra
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ekbVtMv$EHA.1188@.tk2msftngp13.phx.gbl...
> You cannot map names of elements into data using the schema mapping. You
> need to use OpenXML for such mappings.
> HTH
> Michael
> "jimmers" <jimmers@.yandex.ru> wrote in message
> news:b0ede647.0501200116.5025661d@.posting.google.com...
>|||Hello Michael and Chandra
First of all, thank you for prompt responses.
Unfortunately I cannot do XSLT transformation because input file size is
huge (~300 Mb): I made test with .NET XslTransform class on 100 Mb XML input
file and simple XSLT file. The program executed approximately 10 minutes and
then out-of-memory exception was thrown. On disk I got incomplete 42 Mb
result file. The server has 512 Mb memory, P4 2.8 GHz CPU and runs under
Windows 2003 Server Standard Edition.
Right now I stick with the following solution: console application reads
elements from input file with help of XmlTextReader class until size
threshold is reached and saves them in temporary files. Then each resulting
file contents is passed to stored procedure that utilized OPENXML. In other
words, I had to duplicate SQLXMLBulkLoad functionality in Transactional mode
and then use OPENXML feature.
By the way, what is the meaning of HTH signature?
Thank you
Martin Rakhmanov
jimmers@.yandex.ru
"Chandra Kalyanaraman [MSFT]" wrote:

> No, not this exact data file can be mapped using XSD schema. Use XSLT to
> transform this data file to look something like, .
> <?xml version="1.0" encoding="UTF-8"?>
> <root>
> <Item>
> <ItemID>1</ItemID>
> <Property><Name>Property1</Name><Value>...</Value></Property>
> <Property><Name>Property2</Name><Value>...</Value></Property>
> <Property><Name>Property3</Name><Value>...</Value></Property>
> </Item>
> HTH,
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ekbVtMv$EHA.1188@.tk2msftngp13.phx.gbl...
>
>|||Hi Martin.
300MB XML file: What on earth do you have in that? :-) XML files should be
kept as small as possible and not be used as a "database replacement". :-)
HTH means: Hope This Helps
Best regards
Michael
"jimmers" <jimmers@.discussions.microsoft.com> wrote in message
news:214EF7CD-F4BB-4E5A-AD6C-1A57CFA00C12@.microsoft.com...
> Hello Michael and Chandra
> First of all, thank you for prompt responses.
> Unfortunately I cannot do XSLT transformation because input file size is
> huge (~300 Mb): I made test with .NET XslTransform class on 100 Mb XML
> input
> file and simple XSLT file. The program executed approximately 10 minutes
> and
> then out-of-memory exception was thrown. On disk I got incomplete 42 Mb
> result file. The server has 512 Mb memory, P4 2.8 GHz CPU and runs under
> Windows 2003 Server Standard Edition.
> Right now I stick with the following solution: console application reads
> elements from input file with help of XmlTextReader class until size
> threshold is reached and saves them in temporary files. Then each
> resulting
> file contents is passed to stored procedure that utilized OPENXML. In
> other
> words, I had to duplicate SQLXMLBulkLoad functionality in Transactional
> mode
> and then use OPENXML feature.
> By the way, what is the meaning of HTH signature?
> Thank you
> Martin Rakhmanov
> jimmers@.yandex.ru
>
> "Chandra Kalyanaraman [MSFT]" wrote:
>|||300 Mb XML file is maximum size, normally it will be about 100 Mb. It is
generated by external system and I cannot affect this unfortunately.
Thank you
Martin
"Michael Rys [MSFT]" wrote:

> Hi Martin.
> 300MB XML file: What on earth do you have in that? :-) XML files should be
> kept as small as possible and not be used as a "database replacement". :-)
> HTH means: Hope This Helps
> Best regards
> Michael
> "jimmers" <jimmers@.discussions.microsoft.com> wrote in message
> news:214EF7CD-F4BB-4E5A-AD6C-1A57CFA00C12@.microsoft.com...
>
>sql

Is it possible to load such XML file using SQLXML BulkLoad?

Hello
I have an XML file:
<?xml version="1.0" encoding="UTF-8"?>
<root>
<Item>
<ItemID>1</ItemID>
<Property1>...</Property1>
<Property2>...</Property2>
<Property3>...</Property3>
</Item>
<Item>
<ItemID>2</ItemID>
<Property1>...</Property1>
<Property2>...</Property2>
<Property3>...</Property3>
<Property4>...</Property3>
...
</Item>
...
</root>
Number of properties for <Item> is not fixed and their names also not
defined (except ItemID). Is it possible to create XSD schema for
importing this into table using SQLXML BulkLoad facility?
Table:
ItemID PropertyName PropertyValue
=================================
1 Property1 ...
1 Property2 ...
1 Property3 ...
2 Property1 ...
2 Property2 ...
2 Property3 ...
2 Property4 ...
...
Thank you
Martin Rakhmanov
jimmers@.yandex.ru
You cannot map names of elements into data using the schema mapping. You
need to use OpenXML for such mappings.
HTH
Michael
"jimmers" <jimmers@.yandex.ru> wrote in message
news:b0ede647.0501200116.5025661d@.posting.google.c om...
> Hello
> I have an XML file:
> <?xml version="1.0" encoding="UTF-8"?>
> <root>
> <Item>
> <ItemID>1</ItemID>
> <Property1>...</Property1>
> <Property2>...</Property2>
> <Property3>...</Property3>
> </Item>
> <Item>
> <ItemID>2</ItemID>
> <Property1>...</Property1>
> <Property2>...</Property2>
> <Property3>...</Property3>
> <Property4>...</Property3>
> ...
> </Item>
> ...
> </root>
> Number of properties for <Item> is not fixed and their names also not
> defined (except ItemID). Is it possible to create XSD schema for
> importing this into table using SQLXML BulkLoad facility?
> Table:
> ItemID PropertyName PropertyValue
> =================================
> 1 Property1 ...
> 1 Property2 ...
> 1 Property3 ...
> 2 Property1 ...
> 2 Property2 ...
> 2 Property3 ...
> 2 Property4 ...
> ...
>
> Thank you
> Martin Rakhmanov
> jimmers@.yandex.ru
|||No, not this exact data file can be mapped using XSD schema. Use XSLT to
transform this data file to look something like, .
<?xml version="1.0" encoding="UTF-8"?>
<root>
<Item>
<ItemID>1</ItemID>
<Property><Name>Property1</Name><Value>...</Value></Property>
<Property><Name>Property2</Name><Value>...</Value></Property>
<Property><Name>Property3</Name><Value>...</Value></Property>
</Item>
HTH,
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ekbVtMv$EHA.1188@.tk2msftngp13.phx.gbl...
> You cannot map names of elements into data using the schema mapping. You
> need to use OpenXML for such mappings.
> HTH
> Michael
> "jimmers" <jimmers@.yandex.ru> wrote in message
> news:b0ede647.0501200116.5025661d@.posting.google.c om...
>
|||Hello Michael and Chandra
First of all, thank you for prompt responses.
Unfortunately I cannot do XSLT transformation because input file size is
huge (~300 Mb): I made test with .NET XslTransform class on 100 Mb XML input
file and simple XSLT file. The program executed approximately 10 minutes and
then out-of-memory exception was thrown. On disk I got incomplete 42 Mb
result file. The server has 512 Mb memory, P4 2.8 GHz CPU and runs under
Windows 2003 Server Standard Edition.
Right now I stick with the following solution: console application reads
elements from input file with help of XmlTextReader class until size
threshold is reached and saves them in temporary files. Then each resulting
file contents is passed to stored procedure that utilized OPENXML. In other
words, I had to duplicate SQLXMLBulkLoad functionality in Transactional mode
and then use OPENXML feature.
By the way, what is the meaning of HTH signature?
Thank you
Martin Rakhmanov
jimmers@.yandex.ru
"Chandra Kalyanaraman [MSFT]" wrote:

> No, not this exact data file can be mapped using XSD schema. Use XSLT to
> transform this data file to look something like, .
> <?xml version="1.0" encoding="UTF-8"?>
> <root>
> <Item>
> <ItemID>1</ItemID>
> <Property><Name>Property1</Name><Value>...</Value></Property>
> <Property><Name>Property2</Name><Value>...</Value></Property>
> <Property><Name>Property3</Name><Value>...</Value></Property>
> </Item>
> HTH,
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ekbVtMv$EHA.1188@.tk2msftngp13.phx.gbl...
>
>
|||Hi Martin.
300MB XML file: What on earth do you have in that? :-) XML files should be
kept as small as possible and not be used as a "database replacement". :-)
HTH means: Hope This Helps
Best regards
Michael
"jimmers" <jimmers@.discussions.microsoft.com> wrote in message
news:214EF7CD-F4BB-4E5A-AD6C-1A57CFA00C12@.microsoft.com...[vbcol=seagreen]
> Hello Michael and Chandra
> First of all, thank you for prompt responses.
> Unfortunately I cannot do XSLT transformation because input file size is
> huge (~300 Mb): I made test with .NET XslTransform class on 100 Mb XML
> input
> file and simple XSLT file. The program executed approximately 10 minutes
> and
> then out-of-memory exception was thrown. On disk I got incomplete 42 Mb
> result file. The server has 512 Mb memory, P4 2.8 GHz CPU and runs under
> Windows 2003 Server Standard Edition.
> Right now I stick with the following solution: console application reads
> elements from input file with help of XmlTextReader class until size
> threshold is reached and saves them in temporary files. Then each
> resulting
> file contents is passed to stored procedure that utilized OPENXML. In
> other
> words, I had to duplicate SQLXMLBulkLoad functionality in Transactional
> mode
> and then use OPENXML feature.
> By the way, what is the meaning of HTH signature?
> Thank you
> Martin Rakhmanov
> jimmers@.yandex.ru
>
> "Chandra Kalyanaraman [MSFT]" wrote:
|||300 Mb XML file is maximum size, normally it will be about 100 Mb. It is
generated by external system and I cannot affect this unfortunately.
Thank you
Martin
"Michael Rys [MSFT]" wrote:

> Hi Martin.
> 300MB XML file: What on earth do you have in that? :-) XML files should be
> kept as small as possible and not be used as a "database replacement". :-)
> HTH means: Hope This Helps
> Best regards
> Michael
> "jimmers" <jimmers@.discussions.microsoft.com> wrote in message
> news:214EF7CD-F4BB-4E5A-AD6C-1A57CFA00C12@.microsoft.com...
>
>

Is it possible to create an IIF function for SQL Server?

Hello!

I tried the following code:

create function dbo.iif
(
@.Expression bit,
@.TruePart sql_variant,
@.FalsePart sql_variant
)
returns sql_variant
as
begin
declare @.ReturnValue sql_variant

if @.Expression=1
begin
set @.ReturnValue=@.TruePart
end
else
begin
set @.ReturnValue=@.FalsePart
end

return @.ReturnValue
end

It works fine with statements like this:
select dbo.iif(1,'True','False')

However, when trying a "real" expression, an error appears:
select dbo.iif((1=0),'True','False')
Line 1: Incorrect syntax near '='.

How can I work around this?

Thank you very much in advance.define a variant, and set the value to be the expression. Use the variant in your iif function instead.|||would CASE serve the purpose?

Is it a complex sql query?

Hello

I am using stored procedure with sql 2005 (with Visual studio 2005)

I have two tables .. TABLE1 And TABLE2

>From TABLE1 i need to retrive the OrderID's of the 4 most top rows. so
i did:
SELECT TOP 4 OrderID FROM TABLE1 order by OrderID desc

Now what i am trying to do is take the 4 row results (4 OrderID's) i
got from
TABLE1 and check if the 4 rows (4 OrderID's) exist in TABLE2 for a
specific
userID i get by INPUT varible (@.UserId)..

What i want to return is only which OrderID'S existed in TABLE2 for the

specific user.

If only 2 OrderID'S i retrived from TABLE1 exist in TABLE2 i will
return only 2 OrderID's (so i can do my output in visual studio 2005
using the reader())

I would appreciate this if anyone knows how to do this sql query , is
it possible to do this in 1 query? i want to put it in a stored
procedure.I tried to use this query-
SELECT TOP 4 OrderID FROM TABLE1 WHERE exists (SELECT * From TABLE2
where @.UserId=TABLE2.UserID)

But this query shows me the all 4 OrderID's if it finds the USERID in
TABLE2..

What i want to return is only which OrderID'S existes in TABLE2 for the
specific user.

if i have in TABLE1:
OrderID
1
2
3
4

TABLE2:
OrderID UserId
1 1001
2 1002

I want it to return only "2" if the INPUT Parameter of @.UserID is 1002|||Another example..

if i have in TABLE1:
OrderID
1
2
3
4

TABLE2:
OrderID UserId
1 1001
2 1002
3 1002

I want it to return only "2" and "3" if the INPUT Parameter of @.UserID
is 1002|||Hi

Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data in a usable form

You can use something like:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )

Or (better!)

SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
JOIN TABLE2 t2 ON T1.OrderId = T2.OrderId AND @.UserId=T2.UserId

Check out the topics "Using Joins" and "Join Fundamentals" in books online

John

<stockblaster@.gmail.com> wrote in message
news:1137312110.273304.240990@.g47g2000cwa.googlegr oups.com...
> Another example..
> if i have in TABLE1:
> OrderID
> 1
> 2
> 3
> 4
>
> TABLE2:
> OrderID UserId
> 1 1001
> 2 1002
> 3 1002
>
> I want it to return only "2" and "3" if the INPUT Parameter of @.UserID
> is 1002|||Excellent!

Thanks a lot John, it seems to work just fine.. i used the second
example.|||Hello again

Finally i decieded to use SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )

and modifed it to:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE NOT exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )

(Notice the "NOT")
Because i wanted it to return me the OrderID's (from the top 4 of
course) that does not exist in TABLE2 ..

I couldn't do it with the JOIN thingy even if i changed OrderId <>
T2.OrderId ..|||I tried to find this in the documents on the web ..I couldn't find a
way of how to perform this only for the TOP 4 of TABLE1.

Now what happenes:
stockblas...@.gmail.com
Jan 15, 10:01 am show options

Newsgroups: comp.databases.ms-sqlserver
From: stockblas...@.gmail.com - Find messages by this author
Date: 15 Jan 2006 00:01:50 -0800
Local: Sun, Jan 15 2006 10:01 am
Subject: Re: Is it a complex sql query?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

Another example..

TABLE1:
OrderID
1
2
3
4
5
6
7
8
9
10
TABLE2:
OrderID UserId
1 1001
2 1002
3 1002

It will return me: 4 5 6 7 (the top 4 of what it finds)
i use now:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE NOT exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )

Any ideas?|||Hi

I should have said that TOP without and ORDER BY clause is a bit
meaningless.

SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE NOT EXISTS (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )
ORDER BY t1.OrderID

Will return you all rows OrderIds from Table1 where a row in Table2 does not
exist for that OrderId AND has a UserId of @.UserId. With the ORDER BY means
1, 4, 5 and 6 are returned.

To do this using a JOIN, an OUTER JOIN is required.

SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
LEFT JOIN TABLE2 t2 ON t1.OrderId = t2.OrderId AND @.UserId = t2.UserId
WHERE t2.OrderID IS NULL
ORDER BY t1.OrderID

John

<stockblaster@.gmail.com> wrote in message
news:1137325768.003889.45140@.g47g2000cwa.googlegro ups.com...
> Hello again
> Finally i decieded to use SELECT TOP 4 t1.OrderID
> FROM TABLE1 t1
> WHERE exists (SELECT * FROM TABLE2 t2
> WHERE @.UserId=T2.UserId
> AND T1.OrderId = T2.OrderId )

> and modifed it to:
> SELECT TOP 4 t1.OrderID
> FROM TABLE1 t1
> WHERE NOT exists (SELECT * FROM TABLE2 t2
> WHERE @.UserId=T2.UserId
> AND T1.OrderId = T2.OrderId )

> (Notice the "NOT")
> Because i wanted it to return me the OrderID's (from the top 4 of
> course) that does not exist in TABLE2 ..
> I couldn't do it with the JOIN thingy even if i changed OrderId <>
> T2.OrderId ..|||Hello

I am sorry, i didn't explain my self what i wanted to acchive exactly.
For this query:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
LEFT JOIN TABLE2 t2 ON t1.OrderId = t2.OrderId AND @.UserId = t2.UserId
WHERE t2.OrderID IS NULL
ORDER BY t1.OrderID DESC

There is a problem with that..
For example:
Table1:
OrderID
1
2
3
4
5
6
7
8
Table2:
OrderID UserID
6 1001
7 1001
3 1002
4 1002
the result will be:
for user 1001
8
5
4
3

I only need to get 8 and 5 which are the two orderID's the user didn't
have from the top 4 in table 1 ..

can't figure that out :(|||On 15 Jan 2006 10:09:18 -0800, stockblaster@.gmail.com wrote:

>Hello
>I am sorry, i didn't explain my self what i wanted to acchive exactly.
(snip)

Hi Stockblaster,

That's exactly the reason why John suggested you to read the information
at www.aspfaq.com/5006 in his first post to you - posting CREATE TABLE
and INSERT statements and expected output is a much better way to
explain your needs than pure narrative.

If I understand your requirements correctly, then maybe something like
this will work:

SELECT t1.OrderId
FROM (SELECT TOP 4 OrderID
FROM Table1
ORDER BY OrderID DESC) AS t1
LEFT JOIN Table2 AS t2
ON t2.OrderId = t1.OrderId
AND t2.UserId = @.UserId
WHERE t2.OrderID IS NULL

(untested - see www.aspfafq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP|||Hello Hugo..

Very nice! i believe this 1 did the work..

Thanks a lot.. this 1 was stiff.

Is there any good book you can recommend me for sql 2005 (with SQL
Server Management Studio) ... How to upload to a shared web hosting,
when to use relationships, some sql querys examples? all the basics.|||Hi

I am not sure if my interpretation is the same as Hugos!
If all 4 rows returned are below the maximum what should happen?

SELECT TOP 4 t1a.OrderID
FROM TABLE1 t1a
LEFT JOIN TABLE2 t2a ON t1.OrderId = t2a.OrderId AND @.UserId =
t2a.UserId
WHERE t2.OrderID IS NULL
AND t1a.OrderID > ( SELECT MAX(t1b.OrderID) FROM TABLE1 t1b
JOIN TABLE2 t2b ON t1b.OrderId = t2b.OrderId AND @.UserId = t2b.UserId )

ORDER BY t1a.OrderID DESC

John

stockblaster@.gmail.com wrote:
> Hello
> I am sorry, i didn't explain my self what i wanted to acchive exactly.
> For this query:
> SELECT TOP 4 t1.OrderID
> FROM TABLE1 t1
> LEFT JOIN TABLE2 t2 ON t1.OrderId = t2.OrderId AND @.UserId = t2.UserId
> WHERE t2.OrderID IS NULL
> ORDER BY t1.OrderID DESC
>
> There is a problem with that..
> For example:
> Table1:
> OrderID
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> Table2:
> OrderID UserID
> 6 1001
> 7 1001
> 3 1002
> 4 1002
> the result will be:
> for user 1001
> 8
> 5
> 4
> 3
>
> I only need to get 8 and 5 which are the two orderID's the user didn't
> have from the top 4 in table 1 ..
> can't figure that out :(|||Hi

I don't think you will get a single books to cover all these topics,
and you will have to be careful of books based on the pre-release
versions. You may want to check out THe Microsoft SQL Server 2005
Administrator's Pocket Consultant ISDN 0735621071 for configuration
information, and there is always books online. Also check out SQL
Server magazine which has many articles that will be benificial
http://www.windowsitpro.com/SQLServer/

John|||On 15 Jan 2006 15:27:13 -0800, stockblaster@.gmail.com wrote:

>Hello Hugo..
>Very nice! i believe this 1 did the work..
>Thanks a lot.. this 1 was stiff.
>Is there any good book you can recommend me for sql 2005 (with SQL
>Server Management Studio) ... How to upload to a shared web hosting,
>when to use relationships, some sql querys examples? all the basics.

Hi Stockblaster,

I'm sorry, I can't help you here.

Personally, I'm going to wait for Inside SQL Server 2005, that Kalen
Delaney is (hopefully) working on right now. However, the "Inside..."
series are "how does it work" kind of books; you seem to be seeking the
"how do I operate it" kind of books.

--
Hugo Kornelis, SQL Server MVP|||Hello John.

I am not sure i understand, do you mean if table1 contains only two
records? so the top 4 will not work?|||Hi

Sorry for the delayed reply, this one slipped through the net.

My question was related to

There is a problem with that..
For example:
Table1:
OrderID
1
2
3
4
5
6
7
8
Table2:
OrderID UserID
6 1001
7 1001
3 1002
4 1002
the result will be:
for user 1001
8
5
4
3

Do you actually want 3,4,5 as this is less than the maximum for 1001 which
is already 7?

John
<stockblaster@.gmail.com> wrote in message
news:1137449155.173528.136550@.g14g2000cwa.googlegr oups.com...
> Hello John.
> I am not sure i understand, do you mean if table1 contains only two
> records? so the top 4 will not work?