Dependant datasets - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Dependant datasets

Author

Topic

gcowhsu
Starting Member

38 Posts

Posted - 2005-08-31 : 12:21:17

I have a query that returns a customer number and a bunch of other data. With that customer number I need to run another query to get more information about that user.
The problem is that the first query is a 1 to 1 relation ship and then the second query is a 1 to many relationship so I can't just join everything or I am going to have duplicate information about the 1 to 1 relationship.

I have a list that displays all the 1 to 1 information and I added a table to the list to show the 1 to many relationship. I would like to know, if possible, how to pull that customer number out so I can use it for the 1 to many relationship.

I know this is easy with subreports, but I don't think I have permission to use them since they do not export to Excel.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-31 : 12:32:43

How does your first query return the customer number? Is it not a report parameter?

Can you write your second query so that it mimics enough of the first query to retrieve the customer number in the WHERE clause? For example:

Query 1:
SELECT CustNo, FirstName, LastName
FROM Customers
WHERE PhoneNo = '555-1212'

Query 2:
SELECT OrderNo, ItemNo, ItemDesc, ItemPrice
FROM Orders
WHERE Orders.CustNo = (SELECT CustNo FROM Customers WHERE PhoneNo = '555-1212')

By the way, your table should be outside of your list because it has a different Data Source (DataSet).

---------------------------
EmeraldCityDomains.com

gcowhsu
Starting Member

38 Posts

Posted - 2005-08-31 : 12:42:17

By return I meant that it was something that I put in a select statement, not like a value return that you may get from a stored procedure.
Q1
Select Custno, *
from customers

Q2
select telephoneNo
from telephoneNumbers
where custNo = (customernumber from the first query)

so Q2 should run as many times as there are customernumbers.
If I put the table outside of the list then it will just display a list of telephone numbers
Look like
-------------
Customer
customer
customer
customer

telephone
telephone
telephone
telephone

instead i want
customer
telephone
telephone
customer
telephone
telephone

That's why I need the table in the list

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources