Help with combining two queries

I have a stored procedure that needs to retrieve the top 1000 sent items and
the top 1000 received items - each ordered by date. So, effectively, the
most recent 1000 sent items and the most recent 1000 received items. Then, I
need to combine them into one result set and again take the top 1000 items
when ordered by date.
Originally, the stored procedure created a temp table and inserted the
results of each query consecutively, then did a SELECT TOP to get the final
results. Because of high traffic, this is killing the DB server. I suspect
that the queries could be combined into one query. I also tried a UNION of
the two selects, but that doesn't work because I need to do have accurate
results on the subquery first (top 1000 ordered by date). Can anyone help me
determine a more effecient solution, preferrably to combine the two queries
into one? I have slimmed down the two queries significantly to show only the
differences. They are below.
DECLARE @.userName AS VARCHAR(25)
SELECT @.userName = 'MyUserName'
-- Gets the sent items
--
SELECT TOP 1000
@.userName as SenderName,
'Sent' AS SentReceived,
u.[user_name] as ReceiverName
FROM dbo.Email_Type (nolock)
INNER JOIN dbo.Email (nolock) ON dbo.Email_Type.ID = dbo.Email.Type
INNER JOIN dbo.Email_Folders (nolock) ON dbo.Email.Folder =
dbo.Email_Folders.ID
RIGHT OUTER JOIN [Profile].dbo.Contact_history ch (nolock) ON dbo.Email.ID =
ch.source_id_guid
INNER JOIN [Profile].dbo.user_profile u (nolock) ON ch.[contact_user_id] =
u.[user_id]
WHERE
ch.[user_id] = @.UserID
ORDER BY
ch.createstamp DESC
-- Gets the received items
--
SELECT TOP 1000
u.[user_name] as SenderName,
'Received' AS SentReceived,
@.userName as ReceiverName
FROM dbo.Email_Type (nolock)
INNER JOIN dbo.Email (nolock) ON dbo.Email_Type.ID = dbo.Email.Type
INNER JOIN dbo.Email_Folders (nolock) ON dbo.Email.Folder =
dbo.Email_Folders.ID
RIGHT OUTER JOIN [Profile].dbo.Contact_history ch (nolock) ON dbo.Email.ID =
ch.source_id_guid
INNER JOIN [Profile].dbo.user_profile u (nolock) ON ch.[user_id] =
u.[user_id]
WHERE
ch.[contact_user_id] = @.UserID
ORDER BY
ch.createstamp DESCHello, karch
You probably want a query like this:
SELECT TOP 1000 *
FROM (
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
UNION ALL
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
) x
ORDER BY TheDate
However, I think the above query will give the same results as this
query (as long as TheDate is unique among all rows):
SELECT TOP 1000 *
FROM (
SELECT <your columns>
FROM <sent items>
UNION ALL
SELECT <your columns>
FROM <sent items>
) x
ORDER BY TheDate
Razvan|||Yes, but I dont think you can have ORDER BY in the subqueries if you UNION -
I tried this approach and received an error. But, yes, logically that is
what I want to accomplish.
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1146597584.838889.18770@.v46g2000cwv.googlegroups.com...
> Hello, karch
> You probably want a query like this:
> SELECT TOP 1000 *
> FROM (
> SELECT TOP 1000 <your columns>
> FROM <sent items>
> ORDER BY TheDate
> UNION ALL
> SELECT TOP 1000 <your columns>
> FROM <sent items>
> ORDER BY TheDate
> ) x
> ORDER BY TheDate
> However, I think the above query will give the same results as this
> query (as long as TheDate is unique among all rows):
> SELECT TOP 1000 *
> FROM (
> SELECT <your columns>
> FROM <sent items>
> UNION ALL
> SELECT <your columns>
> FROM <sent items>
> ) x
> ORDER BY TheDate
> Razvan
>|||In this case, try another level of subqueries:
SELECT TOP 1000 *
FROM (
SELECT * FROM (
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
) a
UNION ALL
SELECT * FROM (
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
) b
) x
ORDER BY TheDate
Razvan

Help with column Insert

Hi,

I have some values I want put into a table, but the values are from other sources and I dont know how to retrieve them..

I'll show my code, and the bold is explaining what I want inserted and where from. I'd apprechiate if someone could help me with syntax etc. There are 2 about getting value from another table and one about just putting in straight forward text..:

command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";

command.Parameters.Add("@.sendername", System.Web.HttpContext.Current.User.Identity.Name)

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);

command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);

command.Parameters.Add("@.date", DateTime.Now.ToString());

command.ExecuteNonQuery();

Thanks alot if anyone can help me with those three things..

Jon

If 10 people are to receive the message, are there 10 message records or one?

If one, how are the 10 usernames supposed to be formatted? comma-separated values, separated by semi-colons?

|||

I was hoping to have 1 record, with a large list of usernames. I assume it will work if they are seperated by anything, but with a space - peoples messages are called up if the recievername column has their username in it. Will it work if it also has other writing (i.e. other peoples usernames)?

Thanks

Jon

|||

jbear123:

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);

command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);

I just took the time toreally pay attention to what you are doing in business terms instead of just the coding issues.

This is a collection's application? If you sent out a message with my name on it, telling hundreds of other people I hadn't paid up, I would be really angry.

Angry enough to tell you impolite things and never do business with you again.

Depending upon what country you live in or are sending the messages to, it might even be illegal. Particularly if you made a mistake and they did not owe you anything - that would be libel under US law.

That said, I have one other question. The payment instructions are tied to the user who is logged into the page, not the user receiving the message? That was a bit surprising. Or are they tied to the user receiving the message?

As for filling in the values, you just need a few strings to store them in.

Issue a query to get the list of usernames and loop thru them. Use the StringBuilder to concatenate them.

Issue another query to get the payment instructions.

|||

david wendelken:

As for filling in the values, you just need a few strings to store them in.

Issue a query to get the list of usernames and loop thru them. Use the StringBuilder to concatenate them.

Issue another query to get the payment instructions.

This is what I was after. Would you mind explaining this a bit more throughly for someone of a lower coding ability..? i.e. how do I issue the query to get the list of usernames? Compared to the code I posted, how would it be structured? Concatenate them?

Re: Legal issues - Thank you for your concern, but you shouldn't worry about it.

Thanks for your help!

Jon

|||

david wendelken:

As for filling in the values, you just need a few strings to store them in.

Issue a query to get the list of usernames and loop thru them. Use the StringBuilder to concatenate them.

Issue another query to get the payment instructions.

This is what I was after. Would you mind explaining this a bit more throughly for someone of a lower coding ability..? i.e. how do I issue the query to get the list of usernames? Compared to the code I posted, how would it be structured? Concatenate them?

Re: Legal issues - Thank you for your concern, but you shouldn't worry about it.

Thanks for your help!

Jon

|||

You already know how to issue a sql command via the SqlCommand object. Instead of an update command, you need to issue a query:

My guess as to your query would be "select distinct usersname from transactions where itemid = @.itemid"

You will have to pass in the itemid as a parameter, and issue an ExecuteQuery instead of an ExecuteNonQuery. You will be putting the results of the query into a DataReader and looping thru it. Just google or look it up any pretty much any asp book. This is basic, beginner level stuff and it's well documented all over the place - so I'm not going to do it again. :)

Inside the DataReader loop, you can concatenate the usernames you return into a string. It's best to use the StringBuilder class when you are looping. Again, google or look up StringBuilder in the manual.

I think you would be better served slogging thru this step yourself rather than having someone hand it to you. You'll learn it better, and this is bread-and-butter code that you'll use all the time, so it needs to be second nature. I'll keep an eye out on this thread in case you get stuck someplace.

|||

Hi,

I think I have the idea - could you confirm this for me:

I keep the insert commands, but before them I create a command to select all the info that I want inserted (from different tables etc), then using the insert command reference the results of the select command info?

If thats not what yopu meant, would that work anyway?

Thanks,

Jon

|||

I hope it will work as I have done it now..I created datalists showing the data I want to be inserted.

The only thing I cant do is link the results of the datalist (where i selected the data I want(visible=false)) etc to the add parameter section.

E.g:

command.Parameters.AddWithValue("@.sendername", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.recievername",how do I link to results of the datalist here? I'd have to have a comma in between each result);
command.Parameters.AddWithValue("@.message", TextBox2.Text);
command.Parameters.AddWithValue("@.subject", TextBox3.Text);
command.Parameters.AddWithValue("@.date", DateTime.Now.ToString());

Please could you briefly explain, as soon as I know that I can apply it to problemsall over the page and my site will be done!

Thanks alot

Jon

|||

Before you get to this point, create a string to hold the receivername values.

Loop thru the datalist, and for each entry in the datalist, append the next receiver.

|||

I'll give it a go. Just briefly before I leave you alone for a while (sorry!)-

If I create a string, how do I do that for many values (i.e. many usernames),

By loop thru, you mean just use find the string on the datalist?

And what do you mean by append the next receiver? use: + 'next receiver'?

Cheers,

Jon

|||

Hi,

I have:

SqlCommand command = new SqlCommand();
command.Connection = con;

command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";
command.Parameters.AddWithValue("@.sendername", System.Web.HttpContext.Current.User.Identity.Name);
DataView dv = SqlDataSource2.Select(DataSourceSelectArguments.Empty) as DataView;
string receivername = dv[0]["receivername"].ToString();
command.Parameters.AddWithValue("@.recievername", receivername);
command.Parameters.AddWithValue("@.message", TextBox2.Text);
command.Parameters.AddWithValue("@.subject", TextBox3.Text);
command.Parameters.AddWithValue("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();

con.Close();
command.Dispose();
return true;

so far. How can I make values seperate by commas?

Thanks,

Jon

sql

HELP Retrieving remote XML and returning ROWSET

Within a stored procedure, I need to retrieve XML over HTTP from an external
URL, then load some stuff into a recordset and return as such.
Like this, sort-of:
-- *******************************
create procudure queryremote (@.param)
declare @.xmldoc varchar(2000)
set @.xmldoc = (GET 'HTTP://someplaceservesXML.com/script.ext?param=' +
@.param)
sp_preparedocument (@.xmldoc etc.)
SELECT * FROM OPENXML(thexmlthing, "/xpath")
Return
-- *********************************
Just to be clear, this is exactly the opposite of what everyone is trying to
do; many articles on getting XML OUT of SQL server and putting XML data INTO
SQL server, over HTTP.
This requirement is to provide backward -compatibility to
SQL-recordset-aware-only applications. Making an HTTP XML query service
LOOK like an SQL recordset.
Email and post please, thanks a lot!
Richard Weerts
rweerts@.ndis.us
In SQL Server 2000 you have two options:
Do the get on the mid-tier and pass the XML as an argument to the stored
proc.
Write your own extended stored proc to GET the data.
In SQL Server 2005, you can write the function using CLR instead of writing
an extended stored proc.
HTH
Michael
"411XML Richard Weerts" <rweerts@.ndis.us> wrote in message
news:OLr5YmXfEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
> Within a stored procedure, I need to retrieve XML over HTTP from an
> external
> URL, then load some stuff into a recordset and return as such.
> Like this, sort-of:
> -- *******************************
> create procudure queryremote (@.param)
> declare @.xmldoc varchar(2000)
> set @.xmldoc = (GET 'HTTP://someplaceservesXML.com/script.ext?param=' +
> @.param)
> sp_preparedocument (@.xmldoc etc.)
> SELECT * FROM OPENXML(thexmlthing, "/xpath")
> Return
> -- *********************************
> Just to be clear, this is exactly the opposite of what everyone is trying
> to
> do; many articles on getting XML OUT of SQL server and putting XML data
> INTO
> SQL server, over HTTP.
> This requirement is to provide backward -compatibility to
> SQL-recordset-aware-only applications. Making an HTTP XML query service
> LOOK like an SQL recordset.
> Email and post please, thanks a lot!
> Richard Weerts
> rweerts@.ndis.us
>
>