sql query

Agency Table:
AgencyID AgencyName
100           John
101          Dave

Agent Table
AgentID AgencyID AgentName
1            100            John's Friend
2            100            Jonh's another Friend
3            101            Dave's friend



Agency/Agent has 1 to many relationship in the database table.

If I want to make the Many to Many relationship, what you suggest me to do?
solution1368Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Terry WoodsIT GuruCommented:
I would remove the AgencyID column from the Agent table, and create an Agent_Agency table with columns:

AgentAgencyID (just a unique id for the relationship)
AgentID
AgencyID
0
solution1368Author Commented:
can't remove any columns because we already have the data there and also with alot of stored procedure being used.

It is just we cannot modify too many things.
0
Terry WoodsIT GuruCommented:
My guess is that you'll have a bigger mess to deal with if you try to solve the issue while keeping the column there. To start the change, you would insert a row into the Agent_Agency for each Agent, which would store your existing relationships.

Yes, any stored procedures etc would need updating, but in my experience you'll have a bigger headache down the track if you don't do it and implement a compromised schema.

Other experts may like to discuss this further though?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ggzfabCommented:
I agree with TerryAtOpus, as the alternative would be to alter the Agent table to be able to hold multiple agents. This will also cause the stored procedures to have to change as the AgencyID won't be unique anymore...

Just create the relation table tblAgencyAgent like proposed and I would add a StartDate and EndDate, thus allowing to get historical data.
0
sameer2010Commented:
While I agree with above views, other option would be to have unique key on AgentID, AgencyID in Agent table.
Then every time an agency gets attached with agent, the same needs to be updated pro grammatically.
DECLARE @AGENTID AS INT
DECLARE @AGENCY_NEW AS INT
SET @AGENTID=1;
SET @AGENCY_NEW=101;

INSERT INTO AGENT(AgentID,AgencyID,AgentName) VALUES(@AGENTID,@AGENCY_NEW, (SELECT TOP 1 AGENTNAME FROM AGENT B WHERE AGENDID=@AGENTID))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ggzfabCommented:
Good coding sameer2010, but what to do when the agent's name needs to be changed in an agents edit form...

This classical redundancy problem is introduced by not normalizing the n:m relation with a relation table.
0
Terry WoodsIT GuruCommented:
@solution1368, can you please confirm that the accepted solution was the one and only solution posted that solved your problem? If not, then we should use the Request Attention link (just below your question) to get it reopened so the correct solution can be selected.
0
solution1368Author Commented:
please close this question
0
solution1368Author Commented:
I've requested that this question be deleted for the following reason:

Please close this question. The solution provided by the experts do not qualify by the admin in experts-exchange.

Thanks
0
solution1368Author Commented:
Please close this question. The solution provided by the experts do not qualify by the admin in experts-exchange.

Thanks
0
Terry WoodsIT GuruCommented:
It's not that the admin think a solution does not qualify for acceptance; instead the issue is that the author has accepted solutions that aren't actually a solution to the problem, thus their action has been reversed.

As I see it, the author has been given a third chance to accept a solution, but has misunderstood the reason why this sequence of events has occurred and is now wanting to sweep the issue under the carpet by deleting the question!

If the author is unable to select a suitable solution, then a forced accept should be done.
0
Terry WoodsIT GuruCommented:
Reposted as an objection!

It's not that the admin think a solution does not qualify for acceptance; instead the issue is that the author has accepted solutions that aren't actually a solution to the problem, thus their action has been reversed.

As I see it, the author has been given a third chance to accept a solution, but has misunderstood the reason why this sequence of events has occurred and is now wanting to sweep the issue under the carpet by deleting the question!

If the author is unable to select a suitable solution, then a forced accept should be done.
0
solution1368Author Commented:
This codes work to me and logic works as well. Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.