• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Multiple SQL statement output into same datalist

Hi everyone,
I'm running a query that is very specific as to the records to pull up, but I need to pull up all of the supplementary information that the unique identier contains in seperate records that do not fulfill the initial query. Here's an example:

My query:

SELECT DISTINCT table1.ID,table1.reportnumber, table1.frequencycode  WHERE table1.reportnumber=1 and (table1.ID>=500 AND table1.ID<1000) AND (table1.frequencycode=756.71 OR table1.frequencycode=756.82) ORDER BY table1.ID

results in:

ID        ReportNumber         FrequencyCode
500             1                         756.71
550             1                         756.82
575             1                         756.71

However, I need to pull this information, as well as all the other information contained for the above ID's in supplementary records like this:

ID       ReportNumber          FrequencyCode
500             1                          780.0
550             1                          225.0
575             1                          660.0

and I need the information to appear in the same datalist as the first query using the <%#     %> tags.  Any ideas??
 


 
0
Kittrick
Asked:
Kittrick
  • 6
  • 4
  • 4
1 Solution
 
laotzi2000Commented:
How about using a sub query?

SELECT table1.ID, table1.ReportNumber, table1.FrequencyCode
FROM table1
WHERE ID in
(
SELECT DISTINCT table1.ID from table1 WHERE table1.reportnumber=1 and (table1.ID>=500 AND table1.ID<1000) AND (table1.frequencycode=756.71 OR table1.frequencycode=756.82) ORDER BY table1.ID
)

I'm not sure what you exactly want though.
0
 
raterusCommented:
Why can't you use a join?
0
 
laotzi2000Commented:
Do you mean union?
query1 union query2

But I think sub query is simpler if he wants want i think he wants.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
raterusCommented:
I'm kinda confused anyhow, subqueries are rather inefficient since they must run for each row returned, better to do a join if you can.  union's work well too.
0
 
laotzi2000Commented:
yeah, sub query is not very efficient, but join is confusing and hard to think of in this case(have to join itself)
0
 
KittrickAuthor Commented:
Hi laotzi2000,
What I'm looking to do is put the results of two queries in one datalist, but the two queries contain the same unique identifiers, just different information. Also, I have no idea how to reference the same field from two different queries in the datalist. I hope that clears things up.

Kittrick
0
 
laotzi2000Commented:
First, you should think of union the two queries to return only one result set, then you only need to bind to this result set.

And have a look at my query, I think it does what you want in one shot
0
 
raterusCommented:
binding it to the datalist is going to be another problem, you really are going to have to have an iterating control to represent it within the itemtemplate.  It's not all going to be on one pretty line in the datasource.  Might be something for the DataTable.Select() method to do in ItemDataBound of the DataList.  So you have like a datagrid nested within the datalist's itemtemplate, something like that.
0
 
KittrickAuthor Commented:
What I put up was probably too simplistic an example. Ok, what I want to do is actually more complicated in the sense that I have 3 tables using INNER JOIN to get to a definite set of data like this:

SELECT DISTINCT table1.field1,table2.field2,table2.field3,
table1.Id,table2.ID as id2, table1.ID as id3, table2.field4,
 table2.field5,table2.field6, table3.ID as id1,
table2.field7,table2.field8,table2.field9,
table2.field10,table2.field11,table2.field12,
table2.field13,table2.field14,
table2.field15,table2.field16,
table2.field17,table2.field18,table2.field19,
table1.field20,table2.field21,table3.field22,table1.frequencycode
table2.reportnum From (table2 INNER JOIN table3 ON table2.ID=table3.ID)
 INNER JOIN table1 ON table2.ID=table1.ID WHERE table2.reportnum=0
and (table3.ID>=500 AND table3.ID<1000) AND (table1.frequencycode=756.71 OR table3.frequencycode=756.82)
 ORDER BY table3.ID


but I still ned to include in the datalist results from a second query from the ID's in the first query to get the rest of the fields for the ID in seperate records that didn't fit the results of the first query. Can I still use a union??

Kittrick
   
0
 
laotzi2000Commented:
yes, as long as the two result set has the same schema, you can union them to a single result set.

I think that can fit your need.
0
 
KittrickAuthor Commented:
Schema, as in the same fields referenced before the FROM statement??
0
 
laotzi2000Commented:
yeah,
in fact, as long as they have same the type, it's still OK, even if
they came from different columns.
0
 
KittrickAuthor Commented:
laotzi2000 ,
I tried the UNION statement, but it seems to give me not what I want. Ideally, what I was looking to get as a result was the results of the first query, and all supplementary items that didn't fit the first query, but matched with the unique id's found in the first query. The UNION doesn't seem to work for what I'm looking to do.

raterus,
I'm leaning towards your idea of a nested datalist, but I don't know how to get the second resultset to show in the datalist. I have an onItemDataBound event Sub, but I have no idea how to declare the fields for the datalist.  
0
 
raterusCommented:
I think my train of thought here was to originally bind the datalist to a datatable that has all the data, and then during ItemDatabound, you make another selection of the data to get the secondary data, which you bind to a nested datagrid in the ItemTemplate, I guess it would look something like this (sorry on the fly code, probably won't work)

<asp:datalist ..>
  <itemtemplate>
    <asp:datagrid id="dgInner">
      <columns>
        <asp:boundcolumn datafield="ID" />
        <asp:boundcolumn datafield="ReportNumber" />
        <asp:boundcolumn datafield="FrequencyCode" />
      </columns>
    </asp:datagrid>
  </itemtemplate>
</asp:datalist>

Private Sub MyDataList_ItemDatabound(sender as object, e as SomeEventArgs)
  Dim dt as DataTable = DirectCast(DirectCast(sender, DataList).DataSource, DataTable)
  Dim dr() as Datarow = dt.Select("ID = " & DataBinder.Eval(e.item.dataItem, "id"))
  Dim dgInner as DataGrid = e.item.findcontrol("dgInner")
  dgInner.DataSource = dr
  dgInner.Databind()
End Sub

Hope this makes sense, this concept will work as long as your data can be queried in such a fashion,
--Michael
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now