Joining Two tables

hi everyone
i have got 2 tables one is customer and the other is agents. i want to display all the firstnames and lastnames of the customers from the customer table and all the agentnames from the agents table and i wana see all data under one columnIt would help if you described those tables, so now we have to guess how to join those tables. My lucky guess will be that tables look like this:CUSTOMER (cust_id, first_name, last_name, agent_id)
AGENT (agent_id, first_name, last_name)If that's similar to your situation, you might try this query:SELECT c.first_name ||' '||c.last_name ||' - '||
a.first_name ||' '|| a.last_name customer_and_agent
FROM customer c, agent a
WHERE c.agent_id (+) = a.agent_id;Concatenation shows all the data in one column, while outer join shows agents that don't have any customers.

I hope this helps ...|||agreed, table layouts and relationships would have been valuable information

but this sounds more like a UNION than a JOIN to me

select 'customer' as nametype
, lastname ||', '|| firstname as fullname
from customers
union all
select 'agent ' as nametype
, agentname as fullname
from agents

Joining the table relationship from different database

Hi,
Did someone know how to join the table relationship
from different database.
DB1 - Database
Customer - Table
CustID _- Field
Cust_Name - Field
DB2 - Database
CustHist - Table
HistID - Field
Cust_ID - Field
I want to enforce the relationship for this two table in
different database. Let said, I delete one customer
record in DB1. The sql server will enforce the data
integity for me.
Any ideas, Thanks
DaronHi,
You can't enforce the foreign key relationship over two separate databases.
What you need to do is create insert and update triggers on the table and do
it that way.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Daron" <darontcw@.hotmail.com> wrote in message
news:104101c38e07$c11ec3c0$a001280a@.phx.gbl...
> Hi,
> Did someone know how to join the table relationship
> from different database.
> DB1 - Database
> Customer - Table
> CustID _- Field
> Cust_Name - Field
> DB2 - Database
> CustHist - Table
> HistID - Field
> Cust_ID - Field
> I want to enforce the relationship for this two table in
> different database. Let said, I delete one customer
> record in DB1. The sql server will enforce the data
> integity for me.
> Any ideas, Thanks
> Daron|||Thanks, I dont think this is an efficient way to enforce
the table relationship. Because I need to write the insert
and update triggers to my related table. How about my
related tables are many. This really make me headache.
Welcome more ideas.
Daron
>--Original Message--
>Hi,
>You can't enforce the foreign key relationship over two
separate databases.
>What you need to do is create insert and update triggers
on the table and do
>it that way.
>
>--
>I hope this helps
>regards
>Greg O MCSD
>http://www.ag-software.com/ags_scribe_index.asp. SQL
Scribe Documentation
>Builder, the quickest way to document your database
>http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL
Server Extended
>Property Extended properties manager for SQL 2000
>http://www.ag-software.com/IconExtractionProgram.asp.
Free icon extraction
>program
>http://www.ag-software.com. Free programming tools
>
>"Daron" <darontcw@.hotmail.com> wrote in message
>news:104101c38e07$c11ec3c0$a001280a@.phx.gbl...
>> Hi,
>> Did someone know how to join the table relationship
>> from different database.
>> DB1 - Database
>> Customer - Table
>> CustID _- Field
>> Cust_Name - Field
>> DB2 - Database
>> CustHist - Table
>> HistID - Field
>> Cust_ID - Field
>> I want to enforce the relationship for this two table in
>> different database. Let said, I delete one customer
>> record in DB1. The sql server will enforce the data
>> integity for me.
>> Any ideas, Thanks
>> Daron
>
>.
>|||Daron,
There is no other way to enforce relationships over different databases.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Daron" <darontcw@.hotmail.com> wrote in message
news:075001c38e10$ece30df0$a301280a@.phx.gbl...
> Thanks, I dont think this is an efficient way to enforce
> the table relationship. Because I need to write the insert
> and update triggers to my related table. How about my
> related tables are many. This really make me headache.
> Welcome more ideas.
> Daron
> >--Original Message--
> >Hi,
> >You can't enforce the foreign key relationship over two
> separate databases.
> >What you need to do is create insert and update triggers
> on the table and do
> >it that way.
> >
> >
> >--
> >I hope this helps
> >regards
> >Greg O MCSD
> >http://www.ag-software.com/ags_scribe_index.asp. SQL
> Scribe Documentation
> >Builder, the quickest way to document your database
> >http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL
> Server Extended
> >Property Extended properties manager for SQL 2000
> >http://www.ag-software.com/IconExtractionProgram.asp.
> Free icon extraction
> >program
> >http://www.ag-software.com. Free programming tools
> >
> >
> >"Daron" <darontcw@.hotmail.com> wrote in message
> >news:104101c38e07$c11ec3c0$a001280a@.phx.gbl...
> >> Hi,
> >> Did someone know how to join the table relationship
> >> from different database.
> >>
> >> DB1 - Database
> >> Customer - Table
> >> CustID _- Field
> >> Cust_Name - Field
> >>
> >> DB2 - Database
> >> CustHist - Table
> >> HistID - Field
> >> Cust_ID - Field
> >>
> >> I want to enforce the relationship for this two table in
> >> different database. Let said, I delete one customer
> >> record in DB1. The sql server will enforce the data
> >> integity for me.
> >>
> >> Any ideas, Thanks
> >> Daron
> >
> >
> >.
> >sql

Joining Tables Help

I cannot figure this one out.

I have a SQL Server Database.

I have 4 tables I want data from.

Customer

Invoice

Shipper

Customer Address.

Customer is keyed by CustomerID.

CustomerID is a foreign key in Customer Address, and Invoice

Invoice is keyed by InvoiceID

InvoiceID is a foreign key in Shipper.

Shipper has a field, ShipTo.

Each Customer can have multiple addresses. The key for Customer Address is a composite of CustomerID and AddressNo.

Now...

If I had one table that had CustomerID and ShipTo in the same table, I could just link CustomerID to CustomerID and ShipTo to AddressNo.

However, I don't have a single table with that information.

I have:

[Customer] -> CustomerID -> [Invoice] -> InvoiceID -> [Shipper] -> ShipTo/AddressNo -> [Customer Address]

However... I also have [Invoice] -> CustomerID -> [CustomerAddress], with the intent that I can look up a single address per invoice by a combonation of it's CustomerID and the ShipTo address of the relevant Shipping record.

Elsewhere, I'd make a new table to combine those keys, but I can't do anything with this database, as it's the backend of a system.

Please help?I'm not clear what for you need to use the table Customer_Addresses, you have the shipped_address info in the Shipper table.

The first three tables I would link like this (using left outer join):

Customer.CustomerID ->Invoice.CustomerID
Invoice.InvoiceID -> Shipper.InvoiceID|||The shipped_address info you refer to is an integer.

Moreover, it's part of a composite key. You need to know which CustomerID (integer) and which addressID (also integer) to look up the correct record on the Customer_Address table, so you can get street address, state, zip, names, etc.

The main problem I have is that Shipper only has one part of the composite key (the address ID), and the other tables have the other half (CustomerID).|||Why not to post an example of those fields?
:wave:|||[Customer]
CustomerID: 1 FName: John LName: Doe
[Invoice]
InvoiceID: 7 CustomerID: 1 Amount: 32.93 InvDate: 1/1/2002
[Shipper]
InvoiceID: 7 ShipToNum: 8
[Customer Address]
CustomerID: 1 AddressNum: 8 Street: 502 Mack Lane City: Tulsa State: OK Zip: 53234

(fake data)

Each of these (fake) records all relates to the same customer/invoice/address.|||I don't know what CR version you're using but you can create a command .|||I'm using Crystal Reports 8. How can I create this command to join these tables appropriately?

Joining Tables

Hi,
I have a table with fields as partnerid, contractno.
The partnerid field has the Id number which can be a supplier or a customer.
I need to get the partner id(supplier) and the partner id (customers) of that particular supplier only. I tried with self join but the data is data is replicating.

Data in table
PId ContractNo
20045 1567
435 1567
123 1567
345 1678
1004 1678

I need to display the data in the following format.

PId(Supplier) PId(Customer)
20045 1567
20045 435
20045 123
345 1678
345 1004

But I'm getting the data replicated with all records joined every record.
Give the suggestion.Your data doesn't make sense in any way that would give you the query you want? How do you know which PId is a supplier or Customer? Without some kind of a key to indicate that, there's nothing you can do about your issue.|||Hai Madhavi,
Can you show us the query that you have written?

Madhivanan

Joining Table Operation

I have two tables Customer and Customer Details.
Both are joinde with cust_id.
How can I insert data both the tables at the same time using ado.net.
Should I insert individually or can I use JOIN operation for insertion.
Please correct me with an example would be really appreciable.
Thanks in advanceYOu can′t do a multi-insert in a table, you have to wrap these two statement in a transaction and execute / commit these two statements together.

HTH, Jens Suessmeyer.|||Note that the best way to achieve this is by performing both inserts in a transaction. And this is the simplest method to solve this problem. SQL Server 2000/2005 also has INSTEAD OF TRIGGERS that can be used to say update a complex read-only view. With this approach you will simply insert/update/delete from the view & the trigger logic can be perform the necessary DML operations on the base tables. This approach however is overkill for this particular problem.|||Just in addition, transactions are very costly on the server side, so use them wisely.

HTH, Jens Suessmeyer.

Joining Problem

Hi

I am having some problme with a join
There is two tables a customer and orders
The customer table has a unique list of customer
The orders table fills up as they come in

Customer Table
ID Name
1 Cust1
2 Cust2
3 Cust3

Orders Table
ID Customer Product
1 1 Tea
2 1 Coffee
3 1 Milk
4 2 Tea
5 2 Coffee

So there can be multiple orders for one customer

How would I show a list of customer who have never
ordered 'Milk'

This usually does it for me:

select c.ID, c.Name
from Customers c left join Orders o
on c.ID = o.Customer
where c.ID not in
(select o.Customer from Orders o
where o.Product = 'Milk')

It's not the prettiest code but it should work for you.

|||

I think this should be simpler and the output customer will be unique ( no double record )

select c.ID, c.Name
from Customers
where c.ID not in
(select o.Customer from Orders o
where o.Product = 'Milk')

join...

I have a left join between table1 and table2:

select table1.*, table2.contact, table2.telephone from table1
LEFT JOIN table2 On table1.CUSTOMER = table2.customer

... problem is table2 may occasionally have more than 1 entry for a customer (where the repcode is different in each record for the customer)... Instead of the join returning the transaction twice, I want it to only show 1 of the contact details (doesnt matter which record)... How could i do this?

Thanks in advancedoesn't matter which one? oh boy, let's pick the one with the lowest telephone number!!select table1.*
, table2.contact
, table2.telephone
from table1
LEFT
JOIN table2 as T2
On T2.customer = table1.CUSTOMER
and T2.telephone =
( select min(telephone)
from table2
where customer = table1.CUSTOMER )

Join Query

Hi,
I have 3 tables as follows (simplified for posting here):
Table1
--
CREATE TABLE [dbo].[Customer] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerName] [varchar] (50) NOT NULL
) ON [PRIMARY]
Table2
--
CREATE TABLE [dbo].[Supplier] (
[SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
[SupplierName] [varchar] (50) NOT NULL
) ON [PRIMARY]
Table3
--
CREATE TABLE [dbo].[PhoneBook] (
[PBID] [int] IDENTITY (1, 1) NOT NULL ,
[PhoneNumber] [varchar] (50) NOT NULL ,
[ContactType] [varchar] (10) NOT NULL ,
[ContactID] [int] NOT NULL
) ON [PRIMARY]
The ContactType column in Table3 (Supplier) has 2 possible values
(Customer and Supplier) and the ContactID column will either have
CustomerID or SupplierID stored in it based on the ContactType.
Now I am trying to write a query that will return me all records from
the 3rd table (PhoneBook) along with the original names. I have come
up with this:
Query:
--
Select
P.PBID,
P.PhoneNumber ,
IsNull(C.CustomerName, '') + IsNull(S.SupplierName, '') As
ContactName,
P.Contacttype
From
PhoneBook P
Left Outer Join Customer C On P.ContactID = C.CustomerID and
P.Contacttype = 'Customer'
Left Outer Join Supplier S On P.ContactID = S.SupplierID and
P.Contacttype = 'Supplier'
Obviously I'm new to writing queries and I know that there is probably
a much better way to write this query. This query seems inelegant and
probably very poorly written. I am hoping someone can help me come up
with a better queries.
Thanks in advance!
AbhinavHi,
You can use the CASE expression as bellow:
Select
P.PBID,
P.PhoneNumber ,
Case When P.Contacttype = 'Customer' Then C.CustomerName Else S.SupplierName
End As
ContactName,
P.Contacttype
From
PhoneBook P
Left Outer Join Customer C On P.ContactID = C.CustomerID
Left Outer Join Supplier S On P.ContactID = S.SupplierID
Regards,
arik.
"abhinav50@.gmail.com" wrote:

> Hi,
> I have 3 tables as follows (simplified for posting here):
> Table1
> --
> CREATE TABLE [dbo].[Customer] (
> [CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table2
> --
> CREATE TABLE [dbo].[Supplier] (
> [SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
> [SupplierName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table3
> --
> CREATE TABLE [dbo].[PhoneBook] (
> [PBID] [int] IDENTITY (1, 1) NOT NULL ,
> [PhoneNumber] [varchar] (50) NOT NULL ,
> [ContactType] [varchar] (10) NOT NULL ,
> [ContactID] [int] NOT NULL
> ) ON [PRIMARY]
>
> The ContactType column in Table3 (Supplier) has 2 possible values
> (Customer and Supplier) and the ContactID column will either have
> CustomerID or SupplierID stored in it based on the ContactType.
> Now I am trying to write a query that will return me all records from
> the 3rd table (PhoneBook) along with the original names. I have come
> up with this:
> Query:
> --
> Select
> P.PBID,
> P.PhoneNumber ,
> IsNull(C.CustomerName, '') + IsNull(S.SupplierName, '') As
> ContactName,
> P.Contacttype
> From
> PhoneBook P
> Left Outer Join Customer C On P.ContactID = C.CustomerID and
> P.Contacttype = 'Customer'
> Left Outer Join Supplier S On P.ContactID = S.SupplierID and
> P.Contacttype = 'Supplier'
> Obviously I'm new to writing queries and I know that there is probably
> a much better way to write this query. This query seems inelegant and
> probably very poorly written. I am hoping someone can help me come up
> with a better queries.
> Thanks in advance!
> Abhinav
>

Join Query

Hi,
I have 3 tables as follows (simplified for posting here):
Table1
--
CREATE TABLE [dbo].[Customer] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerName] [varchar] (50) NOT NULL
) ON [PRIMARY]
Table2
--
CREATE TABLE [dbo].[Supplier] (
[SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
[SupplierName] [varchar] (50) NOT NULL
) ON [PRIMARY]
Table3
--
CREATE TABLE [dbo].[PhoneBook] (
[PBID] [int] IDENTITY (1, 1) NOT NULL ,
[PhoneNumber] [varchar] (50) NOT NULL ,
[ContactType] [varchar] (10) NOT NULL ,
[ContactID] [int] NOT NULL
) ON [PRIMARY]
The ContactType column in Table3 (PhoneBook) has 2 possible values (Customer
and Supplier) and the ContactID column will either have CustomerID or
SupplierID stored in it based on the ContactType.
Now I am trying to write a query that will return me all records from the
3rd table (PhoneBook) along with the original names. I have come up with
this:
Query:
--
Select
P.PBID,
P.PhoneNumber ,
IsNull(C.CustomerName, '') + IsNull(S.SupplierName, '') As
ContactName,
P.Contacttype
From
PhoneBook P
Left Outer Join Customer C On P.ContactID = C.CustomerID and
P.Contacttype = 'Customer'
Left Outer Join Supplier S On P.ContactID = S.SupplierID and
P.Contacttype = 'Supplier'
Obviously I'm new to writing queries and I know that there is probably a
much better way to write this query. This query seems inelegant and
probably very poorly written. I am hoping someone can help me come up with
a better queries.
Thanks in advance!
AbhinavHow are enforcing data integrity between the Customer and Supplier
tables and the PhoneBook table? Since you can't use a foreign key, [as
it references 2 separate tables], I assume you have a trigger? The FK is
more solid, btw. [if you don't have anything in the database to do this,
consider the fact that anyone that can add data to the database can add
data to this table that does not have valid referenced values.]
You really should have 2 separate phonebook tables, since you have 2
referenced tables (and therefore are representing 2 distinct phonebook
entities), with FKs.
That said, if you can't/won't change it, you can use a UNION ALL.
Select
P.PBID,
P.PhoneNumber ,
C.CustomerName As ContactName,
P.Contacttype
From
PhoneBook P
Join Customer C On P.ContactID = C.CustomerID
where
P.Contacttype = 'Customer'
UNION ALL
select
p.pbid,
p.phonenumber,
s.suppliername as ContactName,
P.contacttype
from
phonebook p
join Supplier S On P.ContactID = S.SupplierID
where
P.Contacttype = 'Supplier'
[to get one phonebook from the 2 i recommend, you could do the same thing]
A J wrote:
> Hi,
> I have 3 tables as follows (simplified for posting here):
> Table1
> --
> CREATE TABLE [dbo].[Customer] (
> [CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table2
> --
> CREATE TABLE [dbo].[Supplier] (
> [SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
> [SupplierName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table3
> --
> CREATE TABLE [dbo].[PhoneBook] (
> [PBID] [int] IDENTITY (1, 1) NOT NULL ,
> [PhoneNumber] [varchar] (50) NOT NULL ,
> [ContactType] [varchar] (10) NOT NULL ,
> [ContactID] [int] NOT NULL
> ) ON [PRIMARY]
>
> The ContactType column in Table3 (PhoneBook) has 2 possible values (Custom
er
> and Supplier) and the ContactID column will either have CustomerID or
> SupplierID stored in it based on the ContactType.
> Now I am trying to write a query that will return me all records from the
> 3rd table (PhoneBook) along with the original names. I have come up with
> this:
> Query:
> --
> Select
> P.PBID,
> P.PhoneNumber ,
> IsNull(C.CustomerName, '') + IsNull(S.SupplierName, '') As
> ContactName,
> P.Contacttype
> From
> PhoneBook P
> Left Outer Join Customer C On P.ContactID = C.CustomerID and
> P.Contacttype = 'Customer'
> Left Outer Join Supplier S On P.ContactID = S.SupplierID and
> P.Contacttype = 'Supplier'
> Obviously I'm new to writing queries and I know that there is probably a
> much better way to write this query. This query seems inelegant and
> probably very poorly written. I am hoping someone can help me come up wit
h
> a better queries.
> Thanks in advance!
> Abhinav
>|||Assuming you reject Trey's idea to have a schema that properly enforces
referential integrity. I don't think your sql is too bad. The only thing
that is "inelegant" is the ContactName portion. I think it should just be
something like this:
Select
P.PBID,
P.PhoneNumber ,
IsNull(C.CustomerName, S.SupplierName) As
ContactName,
P.Contacttype
From
PhoneBook P
Left Outer Join Customer C On P.ContactID = C.CustomerID and
P.Contacttype = 'Customer'
Left Outer Join Supplier S On P.ContactID = S.SupplierID and
P.Contacttype = 'Supplier'
In my opinion, this is better than the union all approach.
"A J" wrote:

> Hi,
> I have 3 tables as follows (simplified for posting here):
> Table1
> --
> CREATE TABLE [dbo].[Customer] (
> [CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table2
> --
> CREATE TABLE [dbo].[Supplier] (
> [SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
> [SupplierName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table3
> --
> CREATE TABLE [dbo].[PhoneBook] (
> [PBID] [int] IDENTITY (1, 1) NOT NULL ,
> [PhoneNumber] [varchar] (50) NOT NULL ,
> [ContactType] [varchar] (10) NOT NULL ,
> [ContactID] [int] NOT NULL
> ) ON [PRIMARY]
>
> The ContactType column in Table3 (PhoneBook) has 2 possible values (Custom
er
> and Supplier) and the ContactID column will either have CustomerID or
> SupplierID stored in it based on the ContactType.
> Now I am trying to write a query that will return me all records from the
> 3rd table (PhoneBook) along with the original names. I have come up with
> this:
> Query:
> --
> Select
> P.PBID,
> P.PhoneNumber ,
> IsNull(C.CustomerName, '') + IsNull(S.SupplierName, '') As
> ContactName,
> P.Contacttype
> From
> PhoneBook P
> Left Outer Join Customer C On P.ContactID = C.CustomerID and
> P.Contacttype = 'Customer'
> Left Outer Join Supplier S On P.ContactID = S.SupplierID and
> P.Contacttype = 'Supplier'
> Obviously I'm new to writing queries and I know that there is probably a
> much better way to write this query. This query seems inelegant and
> probably very poorly written. I am hoping someone can help me come up wit
h
> a better queries.
> Thanks in advance!
> Abhinav
>
>

join problem

Hi All

I am tring to get some data from the customer table and linking it to a view

select ca.account_status, cs.code from customer as ca
inner join v_customer_status as cs on ca.account_status = cs.description

The reference between the tables is the account_status and the description

The error I am getting back is:

Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

Not sure what i means.....

check the collation of the two fields account_status and description
even if they are from the same type they have to be from the same collation
|||The columns in your JOIN might not be of the same COLLATION. You can work around this problem by adding a COLLATE statement on after you column names to explicitly set the collation...

select ca.account_status, cs.code from customer as ca
inner join v_customer_status as cs on ca.account_status COLLATE SQL_Latin1_General_CP1_CI_AS = cs.description COLLATE SQL_Latin1_General_CP1_CI_AS|||

The columns are NOT sharing the same collation, therefore they cannot be joined easily. What you an d is, to force them (or one of them) to use the same (normally one of the already existing one of the columns) collation like te other by using the COLLATE keyword:

select ca.account_status, cs.code from customer as ca
inner join v_customer_status as cs on ca.account_status = cs.description COLLATE SQL_Latin1_General_CP1_CI_AI

(change the collation and the column for your special enviroment)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||thanks for all you help, it sorted the problem

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