Cross References - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Cross References

Author

Topic

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-03-02 : 11:45:58

I have two tables A and B. A is for Agent and B for Clients.
A can have multiple clients and B can have multiple Agents.
Agents have to pay for all clients and Clients have to pay for all Agents(don't ask me why).

So, if I have to find out the complete payment scene. I have to say
Select * from A ....
join B.AgentID=A.AgentId and then----

Select * from B.... where A.agentId=B.AgentId.

I had recommended having a table AB which would have the AgentID mapped to Clients..and a Table BA which would have the ClientID mapped to Agents....

Question is, is that the right thing to do or anybody have any other ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-02 : 11:48:44

this is a bit confusing. Can you post your table structure, some sample data and the result that you want ?

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-03-02 : 11:59:07

quote:
Originally posted by khtan

this is a bit confusing. Can you post your table structure, some sample data and the result that you want ?

KH

I have PMed it to you sir

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-02 : 12:04:46

quote:

Hello khtan

You received the following message from: pareshmotiwala

At: http://www.sqlteam.com/forums/


SELECT     dbo.tblRegistrationComplete.strType, dbo.tblRegistrationComplete.strID, dbo.tblEmployDetailsComplete.strID AS Employ_strID, 
                      dbo.tblEmployDetailsComplete.strType AS Employ_strType
FROM         dbo.tblRegistrationComplete INNER JOIN
                      dbo.tblEmployDetailsComplete ON dbo.tblRegistrationComplete.RegID = dbo.tblEmployDetailsComplete.RegID
WHERE     (dbo.tblRegistrationComplete.strPeriodTitle = '2006') AND (dbo.tblRegistrationComplete.strID = '0084')

SELECT     dbo.tblRegistrationComplete.strType, dbo.tblRegistrationComplete.strID, dbo.tblEmployDetailsComplete.strID AS Employ_strID, 
                      dbo.tblEmployDetailsComplete.strType AS Employ_strType
FROM         dbo.tblRegistrationComplete INNER JOIN
                      dbo.tblEmployDetailsComplete ON dbo.tblRegistrationComplete.RegID = dbo.tblEmployDetailsComplete.RegID
WHERE     (dbo.tblRegistrationComplete.strPeriodTitle = '2006') AND (dbo.tblEmployDetailsComplete.strID = '0084')

Can you post the table structure here with sample data and result that you want.

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-03-02 : 12:36:51

tblEmployDetailsComplete Table B
==========================
Ordinal
Position Column Name Type
1 EmpID decimal
2 RegID decimal
3 strID varchar
4 strType varchar
5 strFName varchar
6 strMInitial varchar
7 strLName varchar
8 strCorpName varchar
9 strAddr varchar
10 strAddr2 varchar
11 strCity varchar
12 strState char
13 strPostalCode varchar
14 strPhone varchar
15 dteDateOfEmploy datetime
16 dteTerminateDate datetime
17 strDuration varchar
18 strBusnIntent varchar
19 strEmail varchar
20 strEmployee varchar
21 strIndirectLobbyist varchar
22 strWaiverNonProfitType varchar
23 IsPaid bit
24 dteUpdated datetime
25 strPage char

tblRegistrationComplete Table A
=======================
1 RegID decimal
2 strID varchar
3 strtype varchar
4 strPeriodTitle varchar
5 strStatus varchar
6 strStatusReason varchar
7 strWaiverStatus varchar
8 strWaiverDesc varchar
9 strEmail varchar
10 strEmailOptOut char
11 strFName varchar
12 strMInitial varchar
13 strLName varchar
14 strNameTitle varchar
15 strCorpName varchar
16 strPhone varchar
17 strBusnAddr varchar
18 strBusnAddr2 varchar
19 strBusnCity varchar
20 strBusnState char
21 strBusnPostalCode varchar
22 strBusnCountry varchar
23 strResAddr varchar
24 strResAddr2 varchar
25 strResCity varchar
26 strResState char
27 strResPostalCode varchar
28 strResCountry varchar
29 strUpdateID varchar
30 strCatID varchar
31 strSSN varchar
32 strBusnIntent varchar
33 strWaiverNonProfitType varchar
34 strComment varchar
35 strContact varchar
===================================

All persons from Table A, can have multiple Employees, for whom he would or would not have paid.
Similarly an Employee from Table B could have hired services of a person or persons from Table A. and not paid for corresponding number of people.

So What i want to find is
When a person from Table A logs in, not only does he see the corresponding clients which he has registered from table B, but also the client who have registered him but not yet paid for this person.

I wouldn't mind having Unions on this..but currently since this table only has 2000 rows, we are living easy. But this may swell up soon. So the question is an intermediate table or better indexing?
Hope this helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources