Novice DBA looking for Advice on Querying the result of a query of a results of a query of a....

Many thanks in advance!

Here's an example of what I'm working on.  Let's say I have a row of data that is returned in a query.  The columns are First, Middle, and last.  We'll say the values in each column, respectively, are 1, 2, and 3.

So I run my first query, something akin to SELECT MIDDLE WHERE MIDDLE = 2.  And we get that row.  Excellent.

What I'm trying to do, is after that statement is executed, run another one on the webpage that looks like SELECT LAST WHERE LAST = [the value that was returned in the previous statment].

And the kicker, here, is I want it to continue querying the returned dataset over and over until LAST = Null.

Any advice would be much appreciated.  I believe I'm looking for some kind of loop, but I'm not really sure where to start.  Thank you!


60% of the time, this code works every time.
0
danielszabo1981
10/27/2008 11:53:47 PM
asp.net.access-datasource 4679 articles. 0 followers. Follow

5 Replies
1033 Views

Similar Articles

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

Okee dokee. First off.....What database are you using...a SQL Server flavor I presume. Secondly, what is the intent of the code. If it is to find the first result with LAST = null from the rows returned by the first query?

If so then maybe you would want to just run a single query ....

SELECT TOP 1  *  FROM NAME_TABLE WHERE LAST IS NULL AND NAMEID IN (SELECT NAMEID FROM NAME_TABLE WHERE MIDDLE=2)

 A little more info and I may be able to help you out.

 

Thanks,
AJ


Arie D. Jones
Principal Microsoft Consultant
Perpetual Technologies, Inc.
www.perptech.com
Blog: www.programmersedge.com
0
ariejones
10/28/2008 2:27:36 AM

Thanks for the response. Here's the situation. I am working on a ticket tracking system in an Access DB, with a .NET front-end. The system generates ticket numbers, and inside the ticket is information specific to that ticket number (for instance, stuff like name, number, problem, etc.).

So, let's say we have created a ticket, and it's ticket number is "1".  Our tech goes out, solves the problem listed in the ticket, and then closes ticket "1". 

Now here's where things get tricky.  Let's say that the person that opened ticket #1 has the same issue again and needs to open another ticket on the same piece of hardware.  Ordinarily, I would just re-open ticket number "1" , but our management wants new tickets opened for every single incident.  Easy enough, but what if you want to see the chain of tickets that are generated for this particular problem?

So I've  created a "previous ticket number" and "forward ticket number" field in our access database that are automatically updated as each new ticket is created.  So, after (let's say) 5 tickets are opened over time for the same problem, we'll have 5 records in our database.  They'll look like this:

Record 1:
Current Ticket Number: 1
Previous Ticket: NA
Forward Ticket: 2

Record 2:
Current Ticket Number: 2
Previous Ticket: 1
Forward Ticket: 3

Record 3:
Current Ticket Number: 3
Previous Ticket: 2
Forward Ticket: 4

etc etc etc.

Ok.  So I've got everything working so far.  Now, let's take this to the front-end.  I've got a FormView that can dispaly records.  No problem:
SELECT * FROM [TICKETTABLE] WHERE [Current Ticket Number] = 2.
This returns record number 2 and associated fields.  Easy enough.

But what i'm trying to do is display the chain of tickets that are related to this particular record as well.  Essentially running a query that looks at the Forward Request Number field in the FormView and returns the record where said Forward Request Number is equal to another record's Current Request Number,  something like:

SELECT [Forward Request Number] FROM [TICKETTABLE] WHERE [Current Request Number] = [Forward Request Number in FormView] 

Theoretically, this will return the next record in the chain.  I'd like to loop this so that it keeps returning those records until the end of the chain.  Then run that same query again on the returned results to obtain Forward Request Number in the chain. And again, and again.

And as an added bonus, I need to do the same thing for the Previous Request Number field, too.

I'm anxious to hear any ideas you guys may have.  Thank you!


60% of the time, this code works every time.
0
danielszabo1981
10/28/2008 6:12:47 PM

Perhaps you should rethink your table design.  I would have a table for Issues or the InitialTicket, then a table of Actions for each ticket, on the basis that there may be one or more actions related to a ticket.  Every time there is another action relate to a unique ticket, I would add the action to the Actions table, plus a date stamp and the InitialTicket primary key value as a foreign key.  That way, you can retrieve all actions for a given ticket nicely.

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
10/28/2008 9:18:16 PM
I agree completely. That was my first thought. However, I've been given this model and told to "make it happen". Otherwise, I'd be with you all the way. :)
60% of the time, this code works every time.
0
danielszabo1981
10/28/2008 11:35:30 PM

Do you mean that you are forbidden from changing the structure at all, despite the fact that it is poor (well, Wrong, actually) and makes what you need to do with the database a lot more complex?  An experienced DBA would put their foot down and make the required changes Wink

Otherwise, you will just have to make recursive calls to the database in both directions, placing the resulting records into some kind of temporary structure from which you can re-order the results in whatever way you need.  A Temporary table in Access springs to mind, but these are horrid.  The database just grows and grows, unless you also add code to compact and repair the database each time.  So a DataTable will do you.  In fact, the more I thnk about it, the harder I would put my foot down.  Whatever result you end up with is going to be very inefficient.  And, basically, not right.  Yuk.

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
10/29/2008 7:32:47 AM
Reply:

Similar Artilces:

Querying a Queried result...
Hi, is there such a thing as querying a queried result? Example, query 1 produces Result A. Result A's table is stored 'somewhere' to be queried by query 2 which produces Result B If there is, could you kindly direct me to a website with the appropiate article on this topic? Thanks, -Gabian- Yes there is, querying a View is querying a query rewrite. Send me an email the address is in my profile I will send you a Views tutorial I wrote a while back and run a search for query results from View in your BOL(books online) for code samples. Hope this helps. Kind regards, ...

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...

If one query returns any result then the result from other query should be eliminated..
HI, I want to create a query which returns allowed reports for the user. There are two different queries which returns the allowed reports. One returns the reports according to user and other reutrns reports according to the role of user. e.g.Select ReportID from UserReports and  select ReportID from RoleReports (in original scenario both queries are quite complex and large) Requirement is if the first query returns any row then the result of the from the second query should be ignored. IF(Select ReportID from UserReports > 0) Begin Select ReportID from Us...

Query a Query with multiple results
Hi,New to .Net and SQL.  I have two tables that I have joined together.  RentalControl_Main has the rental informationd and an Adjuster ID that links to the ADjuster table and the adjusters name.  I am trying to create a report that gives the "Single" adjuster name and the totals for all of their contracts.  I have a details report that gives each contract info. for each specific adjusters rentals.  However, I want to just list the adjuster once and give all of their totals.  In my SQL statement I have all of it written out and just need to knowwhat to do in pla...

How to query a query...
I'm working on converting an Access DB that I created into an ASP.NET application (and attempting to learn ASP at the same time...). One of my queries was very complex and required running a different query first, and then working off of that one. Any suggestions on the best way to do this in ASP? The following is a sample of code I'm using so you can see the type of data-binding I'm using. Thanks for the help! myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +"Data Source=" + Server.MapPath("/EquipmentCheckout.mdb")) strS...

Query a Query
I have a result set in a query. Is it possible to query this query? (no specific SQL dialect, can it be done generically?) Petros -- PETROS GAVRIELIDES wrote: > I have a result set in a query. > Is it possible to query this query? > > (no specific SQL dialect, can it be done generically?) There is a generic answer to your question: Yes, there is a construct called a sub-query that allows you, in 1 SQL statement, to query from a query. But, generically speaking, not all DB engines support this construct. For instance, Firebird only added it just over a y...

Query about Queries
Hello all, As a relatively new user to Sybase I am hoping someone can point me in the right direction. We are getting ready to go live with our database and I am wondering what types of queries I should be running for system performance purposes. If anyone has any suggestions they would be more than appreciated. Thanks in advance, Jerry ...

How to Query A Query
I have been trying to build a crosstab report that reports everydate within a selected range across the top and specific row information. 7/1 7/2 7/3 . . . Name Detail1 Detail2 Detail3 Name Detail1 What I get as output is only the dates that contain information in the rows. I can get the information I need by using a query within a query (nested). Can a nested query be done in Informaker 5? What about version 6 or 7? Thanks for your help Convert your SQL to syntax, and you can write anything you want. So if your DB support...

Using results of first query in other queries
Hi, I would like to use the result table of the first query in a number of other queries. How do I do this ? Thanks. there are prbly more efficient ways to do it and it all depends on how often you use those result sets...is it in one trip or would you need the results across multiple trips...etc. one way would be to create a table variable and insert the result set into it. and use the table variable as a regular table to query against. or you can also write a view and query against the view. hth***********************Dinakar NethiLife is short. Enjoy it.*********************** ...

Search query returns XML results. How do I get the results back to the page that submitted the query or a new page.
Hello,I have a search page with a text box and button. When I search for a keyword, my page sends parameters to a search appliance that returns XML results. How can I get the results back to my search page or some other page on my site and not the search appliance. I know I need xlst to transform the XML results but, how do I get the results back on my site instead of the appliance on by just entering a keyword and clicking on the search button. Thanks for any help! I'm assuming that you are doing a direct form based post to the search appliance which is a service taking a keyword and retu...

Results of One Query Used In A Second Query
I don't know the correct approach to this problem. Nested Queries perhaps? I have users enter stock symbols into a textbox. The symbols are then written in a table that has 3 columns named UserID, Portfolio_Name and Portfolio_Symbols. In order to look the symbols up (Portfolio_Symbols), I search by UserID and Portfolio_Name; this returns a string consisting of the symbols. What I need, however are the results of (another) query based on these symbols(Portfolio_Symbols)--the result would be various pieces of data for each company (symbol)being returned to a dataset that will be read...

Return result from one query as a column in other query
I'm having a bit of a trouble explaining what I'm trying to do here. I have 3 "source" tables and a "connecting" table that I'm going to use tblContacts - with contactID, ContactName etc tblGroups - with GroupID, GroupName tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups) They are related in a table called tblContactsGroupConnection - with ContactID, GroupID and SubGroupID One contact can be related to many subgroups. What I want is a list of all contacts, with their IDs, names and wh...

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 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. ChrisDont 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. Yes this is possible. This is what is called a "Nested Query" in SQL. ...

query results: xpath:query vs. token-src-dn
Hi, I am trying to read the "Last Referenced Time" attribute through the = token-src-dn instead of an xpath query and it does not return any value = (though there is one, of course :-). the query operation issued by the = policy looks absolutely the same regardless if I use xpath:query or = token-src-dn, yet I'll only get an <instance> in return with xpath:query, = not with token-src-dn... any ideas? 17:07:00 8A1CEBB0 Drvrs: TEST_PWNotify PT: Action: = do-set-local-variable("LastRunTime",scope=3D"driver",token-xpath("query:rea= dObject(...

Web resources about - Novice DBA looking for Advice on Querying the result of a query of a results of a query of a.... - asp.net.access-datasource

Wanderers coach Tony Popovic slams Sydney FC's Graham Arnold for querying players' integrity
Western Sydney coach Tony Popovic has lashed out at Sydney FC coach Graham Arnold for suggesting Wanderers players celebrated Ali Abbas' injury ...

BBC News - Querying Nato's rapid reaction force
Nato's announcement of a beefed-up rapid reaction force raises big questions for many of its key member states, says the BBC's Jonathan Marcus. ...

Querying EntityFramework Views without a Key
By definition an Entity should have an Identity. An issue that often comes up with EF is querying views which do not have a primary key. No key, ...

Facebook unveils Presto engine for querying 250 PB data warehouse
At a conference for developers at Facebook headquarters on Thursday, engineers working for the social networking giant revealed that it’s using ...

Manually assigning .related('somerelation') without querying database? · Issue #919 · tgriesser/bookshelf ...
bookshelf - A simple Node.js ORM for PostgreSQL, MySQL and SQLite3 built on top of Knex.js

Cloudera Impala Brings SQL Querying To Hadoop
Cloudera's SQL-on-Hadoop tool hits general release, but will it satisfy demands for faster, easier exploration of big data?

Pivotal juices Hadoop with in-memory database and SQL querying
Pivotal, an EMC/VMware spin-off that has big plans to deliver big data analytics through platform as a service, has whisked the

Link Building Query Theory: 7 Crucial Keyword Types for Link Prospect Querying
These seven common types of prospect keywords will expand how you think about link prospecting and help you become a smarter, more strategically ...

Microsoft updates Power BI for Office 365 with data search, natural language querying, and 3D visualizations ...
Emil Protalinski / The Next Web : Microsoft updates Power BI for Office 365 with data search, natural language querying, and 3D visualizations ...

MH370 report reveals 17-minute delay in querying missing plane
Air traffic controllers took four hours to launch search and rescue operation, according to files released by Malaysian government

Resources last updated: 1/19/2016 8:32:15 PM