2 datasources, 1 datagrid

[aaronjonmartin]

Ok could somebody tell me if the following is possible and how I could go about achieving it?

I have two querys the first simply returns a column of IDs and another column which is the related names to these IDs. The second query takes an ID (ones from the previous table) and returns the number of times this ID appears in another table (i.e. number of times that ID appears as a value in a foreign key column in another table).

What I want to achieve is to have a datadrid or even simply a repeater which displays this information on one row.

e.g.

_________________________________________________
|    ID      |      NAME     |   NumberAppears   | 
_________________________________________________
|    12      |     Aaron     |       56          |

Bit of a crude diagram but I hope you get the idea. Basically data from two querys are being displayed in one table. How can I do this?

"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)

 

[jbenson001]

If you are using sql server or sybase, use a stored procedure to get the results, then return on result set. The sql will be much easier to write that way.

Jim

 

[aaronjonmartin]

sorry if i wasnt clear, yes I am using Sql Server and stored procedures to return the data. They are two separate stored procedures which return the two sets of data as described in my first post.

If you substitute the word query for stored procedure in my first post it might make more sense.

"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)

 

[jbenson001]

Actually what I was suggesting is have only ONE sp and return one result set. I don't believe there is any reason to have 2 quries to the the results you want.

 

[DotNetGnat]

i am sure you can combine your two queries into a single query using joins, union or something else...as jim pointed out make it a single stored procedure...

-DNG

 

[aaronjonmartin]

well my first idea was to try and combine them somehow but Im not sure how I can do it. How can you get all the ids from a table, then count how many times each appears in another table in one stored proc?

"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)

 

[DotNetGnat]

can you provide some sample data from your tables and show the kind of result you are looking for...

-DNG

 

[DotNetGnat]

something like thsi:

Select A.ID, B.IDCount 
from table1 A INNER JOIN
(Select ID, Count(ID) as IDCount from table2 group by ID) B
ON A.ID = B.ID

-DNG

 

[aaronjonmartin]

Ok my exact situation is I have a table which contains information about a users. It has a unique ID, a username column and a column which contains the date each user was entered into the table.

I have a second table which contains predictions for football 5 football matches. It contains a unique ID, five columns which relate to football matches and contain either 1, x, or 2 to signify the predicted result. It also contains a column which stores the userid of the user who predicted the results of the football matches.

What I want to display is the ID and persons name from the user table, and a count of how many rows their userid appears in the predictions table (i.e. how many predictions they have made).

And I want to display this in one datagrid or repeater as mocked up in my first post.

"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)

 

[aaronjonmartin]

Thanks for the suggested code DNG I will give it a try.

"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)

 

[BoulderBum]

As a trivia note, it's also pretty easy to merge data on the application server via DataSets and the DataSet.Merge() method.

After merging, you'd simply bind the grid to the merged DataSet.