Join one to many relationship

hi all geeks,

I have a problem regarding joins.

I have 2 tables,

Customer ( Customer_code, Agent1, Agent2, Agent3)

Agent( Agent_Code, Agent_name)

and data is like this:

Customer_codeAgent1Agent2Agent3112322343143

Agent Table

Agent_CodeAgent_name1X2Y3Z4P5Q

Now I want to retrieve all customer_code with their corresponding agent names, like

1 X Y Z

2 Y Z P

3 X P Z

, any suggestions please,

thanks a lot.

You can use a query like this

Select Customer_code,

(select agent_name from Agent where agent_code=Agent1),

(select agent_name from Agent where agent_code=Agent2),

(select agent_name from Agent where agent_code=Agent3)

from Customer

|||

thanks a lot Mr. Abdul

|||

if you really need relations use this code:

createtable #Cust( customer_codeint,

Agent1int,

Agent2int,

Agent3int)

createtable #agents(AgentCodeint,

AgentNamevarchar(10))

insertinto #cust

SELECT 1,1,2,3

insertinto #cust

SELECT 2,2,3,4

insertinto #cust

SELECT 3,1,4,3

insertinto #agents

SELECT 1,'X'

insertinto #agents

SELECT 2,'Y'

insertinto #agents

SELECT 3,'Z'

insertinto #agents

SELECT 4,'P'

insertinto #agents

SELECT 5,'Q'

select*from #Cust

select*from #agents

Select customer_code,

a1.AgentName [Agent1Name],

a2.AgentName [Agent2Name],

a3.AgentName [Agent3name]

from #cust

leftjoin #agents A1

on Agent1= A1.AgentCode

leftjoin #agents A2

on Agent2= A2.AgentCode

leftjoin #agents A3

on Agent3= A3.AgentCode

droptable #cust

droptable #agents