can i do an sql query then use the result of that in another query...

for instance i need to find value of storyId from one table then make sure i do not pull that record from another table. is this possible

0
jolyon
8/29/2007 11:42:54 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

6 Replies
1148 Views

Similar Articles

[PageSpeed] 50
Get it on Google Play
Get it on Apple App Store

With SQL you can do something like this:

select * from TableB where StoryIdValue not in (select StoryIdValue from TableA)

This is just 1 of many possible solutions.

Chris


Dont forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
scorpioafrica
8/29/2007 11:57:18 AM

Yes this is possible. This is what is called a "Nested Query" in SQL.

A SQL nested query is a SELECT query that is nested inside a SELECT, UPDATE, INSERT, or DELETE SQL query. Here is a simple example of SQL nested query:

SELECT Model FROM Product
WHERE ManufacturerID IN (SELECT ManufacturerID FROM Manufacturer
WHERE Manufacturer = 'Sony') 

HTH

Cheers

Vishal Khanna

(pls mark as answer if reply helps)


Vishal Khanna
.NET Interviews & FAQs @ www.dotnetUncle.com


Click Mark as Answer if the reply helped you.
0
moredotnet
8/29/2007 11:59:53 AM

If you do want to keep the queries seperated from each other, you could turn them both into stored procedures. The first stored procedure will return a value, and you can define that value as an input-parameter of the second stored procedure.

 

 

Kind regards,
Wim


Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
0
deblendewim
8/29/2007 12:11:25 PM

Thanks for your help Wim, could you provide an example of what the sqldatasource would look like with this input parameter in?

0
jolyon
8/29/2007 12:17:05 PM

select name from emp where sal < (select max(sal) from emp)

 

  here in this case i m going to select the name of the employee whose salary is less than the maximum sal

 

 

Ashwini


Ashwini

Plz mark as ANSWER, if my POST help u

0
AshwiniK
8/29/2007 12:23:31 PM

jolyon:
Thanks for your help Wim, could you provide an example of what the sqldatasource would look like with this input parameter in?
 

Hi jolyon,

The example below is just fictive. So copy-pasting the code into your code will most likely not work!
You can use it as an example  tough.

What I did is the following:

  1. I instantiate a connection
  2. I use that connection for 2 sqlCommands I want to run agianst the connection.
  3. The first sqlCommand executes a SP (stored procedure) which returns only one result. You have to make sure only one result is returned from your SP, or otherwise don't use the ExecuteScalar but something else, like the executeReader.
  4. Then the returnValue of the first Stored Procedure is stored in an Integer.
  5. That value is used in the second sqlCommand, which is also a stored Procedure.
  6. The resultset of the 2nd stored procedure is stored in a DataTable.

Ok, you have to keep a few things in mind:

  • The above steps is just one way to do it. For example: You could modify your first SP so it holds an output-Value. Then when you execute it, the returnValue is for example the storyId. Look on google, or this article to get to know more about stored procedures: http://aspnet.4guysfromrolla.com/articles/062905-1.aspx  or http://support.microsoft.com/kb/306574  
  • The code I added in this example will not work. It's just to give you a feeling of how to work with SP's
  • All your logic will rely in your SP's. I mean, the logic of getting the right storyId (Stored Proc. 1) and the logic of getting rows where storyId is not like storyId selected in SP1 (Stored Proc2).

 

EDIT: (and here is the example offcourse :) )

 

Imports System.Data
Imports System.Data.SqlClient

Partial Class Default9
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connection As String = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"
Dim myConnection As New SqlConnection(connection)
Dim dt As DataTable = New DataTable

myConnection.Open()

'I'm sure thatmyFirstStoredProcedure will only return one result.
'That's why I can use ExecuteScalar!

Dim myCommand1 As New SqlCommand("myFirstStoredProcedure", myConnection)
myCommand1.CommandType = CommandType.StoredProcedure
Dim myStoryId As Integer = Convert.ToInt32(myCommand1.ExecuteScalar())

Dim myCommand2 As New SqlCommand("mySecondSP", myConnection)
myCommand2.CommandType = CommandType.StoredProcedure

'the code below which is commented out is a longer way of defining the parameter.
'Dim group As New SqlParameter("@group", SqlDbType.VarChar, 15)
'group.Direction = ParameterDirection.Input
'group.Value = GroupValue
'myCommand.Parameters.Add(group)

myCommand2.Parameters.AddWithValue("@group", myStoryId)

Dim reader As SqlDataReader = myCommand2.ExecuteReader(CommandBehavior.CloseConnection)
dt.Load(reader)
reader.Close()
End Sub
End Class
  

 

I hope this answers your question.
If not please let me know!

Kind regards,
Wim


Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
0
deblendewim
8/30/2007 9:31:28 AM
Reply:

Similar Artilces:

Can sql queries be nested in sql queries?
I am having problems with a template that uses the information from one sql query to generate a query on another table. Let me be more specific. The template in question receives a category ID value from a web page that provides a menu of several categories to choose from. Each Category ID represents several Items or products to choose from. When the template receives the category ID, the first thing it does is run a query on my item_category table (this table lists all of the items and associates them with the appropriate category). This query returns several itemIDs that are all...

Combine SQL query result with XML query result
Hi all, Hopefully someone can help me with this: I know how to setup and run a query on an SQL db and I can probably work out how to query an XML file, but how do I take the results of each and combine them into one collection (i'm presuming a dataset)? Once i've created the combined collection, i'd want to order it by some universal identifier present in both datasources e.g. product ID. It's worth pointing out that the XML file would not be in the same format/use the same field names (or number of fields) as the SQL query, so I guess I might need to bind my query resu...

sql or my sql query
hi,in my database  i have the two tables...1)programs                                2)reservedin the programs...two fields 1)program id           2)maxseats................these details wil be entered by adminin the reserved table  two fileds ..1)PRogramid        2)statusmy requirement is .. i need the available seats..meansif any body is intersted in  any program...they wil select the program...then status becomes approved(1)..then i need the o/p as ...

How to add users to a SQL Server DB used for authentication using SQL query
Hi, I am looking for a simple way to add users manually to a SQL Server DB (set up using aspnet_regsql.exe) via a SQL query. What are the required values, that have to be set in the different tables? Thanks in advance, Markus By default SqlMembershipProvider calls the aspnet_Membership_CreateUser stored procedure to create a user, you can use T-SQL statment like sp_help aspnet_Membership_CreateUser to learn how to use it.Welcome to my SQL/ASPNET forum for Chinese http://51up.org/bbs/forumdisplay.php?fid=38...

how to connect sql server using sql query?
Hi all, Please explain how to connect sql server using sql query?. My problem is need to get the data from one server table and insert into another server tables using stored procedure. It is possible by using C# coding. But i need to do using stored procedure. becoz each server have own connection string, username and password.  Please anyone help me? Thanks in advance!!! Hi, You can create a linked server between the two databases and then fire the queries. http://www.sqlservercurry.com/2008/03/how-to-set-up-your-database-for.html   HTH,Suprotim AgarwalA...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

Srored procedure, sql query not using values in the query
ISSUE: I am attempting to construct a stored procedure that will select select information between two time periods. Well I have tried a couple of queries to find out what would happen to performance. What I have found is the query where the range of dates is entered, hard coded, the Optimizer uses the index. If I use a variable or a calculation I get a table scan and the dates are not used at all in the query. Why is this happening, forcing the index has no effect, see last example. I am using ASE 11.92 on a SUN server, Sun_srv4, sybase build 1031. below are the results of query ...

C# Ado.net Sql query parameter query
Hi All, I'm trying to pass in a parameter value from an array in a loop that is used in a sql query and the results are populated to an xml file. The trouble is that I'm only getting the colums values in the outputted xml file. So I feel that the paramter is not being read. So can anyone help as I'm really stuck on this one. The code is as follows : public void DisplayUserInfo() { ArrayList UserIdArrayList = IdentifyUserID(); foreach(string ShowUserIDString in UserIdArrayList) { try { SqlConnection SqlConn = new SqlConnection(DBConnString)...

I use SQL 2000, can you use one Delete query to delete 2 tables?
this is my Delete Query NO 1 alter table ZT_Master disable trigger All Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0) alter table ZT_Master enable trigger All   I have troble in Delete Query No 2 here is a select statemnt , I need to delete them select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey)  And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths wher...

how can i do response.write sql to see sql query(values)
In regular asp i used to do a lot of response.write sql to see my query, for debugging purposes. Can you please tell me how i can do the same for the following code, i used here the dataset. ****************Code*********** Dim UpdateCmd As String = "UPDATE tbl_labels SET eng_lbl = @engl, " & _ "esp_lbl = @espn, fr_lbl = @fren, ger_lbl = @german where id = @Id" MyCommand = New SqlCommand(UpdateCmd, MyConnection) MyCommand.Parameters.Add(New SqlParameter("@Id", SqlDbType.int, 9)) MyCommand.Parameters.Add(New SqlParameter("@engl&qu...

Easy SQL question. How to display query results in Query Analyzer
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says. Command(s) completed successfully. What I really need it to do is to display the results of the query. Does anyone know how to do this? declare     @SniierId as   uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare    @SniierAlias  as nvarchar(50)declare    @AlwaysShowEditButton  as bitdeclare     @SniierName  as  nvarchar (128)/* Check access for Snii...

stuck on SQL Query
Hi, In my sql 2005 database I have a Country Table and a Footballer Table as below.   Country CountryID (int) P-K Country Name   Footballer Name (int) P-K Team (varchar) CountryID (int) (F-K)    i have 50 footballers in my Footballer  Table, 10 from each country below spain, england, france, ireland, sweden My Country DDList1 on footballer.aspx is binded to the County Table -- but instead of showing all 268 countries to the visitor, I just want the list to be populated with the 5 that are in my footballer database. I am using ...

How can I use SQL queries in code behind.
I've been using ASP.NET (came from classic asp) for about a year now and love how easy it is to code in this new language and how easy it is to bind data to Gridviews etc.  I used to have to create SQL connections in code and then use something like RS("Name") to get the query result field I needed the value of. I need this functionality now in ASP.NET but all the tutorials I have found talk about binding objects to datasources.   I need to perform a sql query in my VB code behind page that will look up a single row from a SQL database t...

VB .NET Dataset SQL query results
Hi, I'm hoping someone can shed some light on how I can address variables returned to a dataset via a stored procedure.I've coded an AJAX Autocomplete control but due to the restriction of the control (only being able to return one value) I now need to use the returned value to search my DB.  The stored procedure used for the Autocomplete returns the key information (and the description).  I'm assuming the key info is stored within the dataset. So my question is, how do I read the key information into a variable for use elsewhere?Stored proc Select Statement; SELECT SU...

Web resources about - can i do an sql query then use the result of that in another query... - asp.net.sql-datasource

Hillary: Elect me and I’ll get to the bottom of UFOs and Area 51
Old and busted : Donald Trump actively appeals to the fringies! New hotness: Media to salute Hillary Clinton’s inclusiveness in 4, 3, 2 … Hillary ...

Samsung Reveals The ‘Family Hub’ Refrigerator: Forget The Galaxy Note 5 And Galaxy S6 Edge
If you thought that Samsung came up with revolutionary products when it came to smartphones, wait until you see what they have just unveiled. ...

The Quiet Crisis in the Kingdom of Saudi Arabia
TIME The Quiet Crisis in the Kingdom of Saudi Arabia TIME The latest uproar between Saudi Arabia and Iran has spread far and fast since the ...

John Oliver: Revised Resolutions
John Oliver helps you revise the New Year’s resolutions you’ve already failed to keep.

Official ‘Star Wars: The Force Awakens’ Script Reveals New Details: Is Rey A Skywalker?
Star Wars: The Force Awakens is nothing short of a global phenomenon, surpassing Titanic to become the No. 2 movie of all time and well on its ...

General Motors Invests $500m in Lyft, Forms Partnership
General Motors invests $500m in Lyft as part of plan to speed development of self-driving cars

Oculus to start taking virtual reality headset orders
Facebook-owned Oculus VR will begin taking orders for its Rift virtual reality headsets on Wednesday, as the doors of Consumer Electronics Show ...

Deadly earthquake strikes India's Manipur state and Bangladesh
A 6.7 magnitude earthquake hit India's remote northeast region. At least eight people were killed and 100 others were injured. India's Meteorological ...

US sues VW over emissions-cheating software in diesel cars
The Justice Department sued Volkswagen on Monday over emissions-cheating software found in nearly 600,000 vehicles sold in the United States, ...

Memorial for Artist-Teacher Killed in Belltown Shooting Will Be This Sunday
Brent McDonald (left) was shot and killed in Belltown in the early hours of December 13. Danielle Logan (right), his longtime partner, has described ...

Resources last updated: 1/4/2016 11:51:00 PM