help me join same table

[rusdyrip]

i have 2 table

table Person
ID Name Type
001 A Customer
002 B Sales

table Invoice

InvoiceID CustomerID SalesID Quantity
0000001 001 002 10


i want to join like this

InvoiceID CustomerID Customer Name SalesID SalesName Quantity
0000001 001 A 002 B 10

pls tell me how to do it

thx

[Delerna]

SELECT InvoiceID,CustomerID,Customer,Name,SalesID,SalesName,Quantity
FROM Person
Join Invoice on Person.ID=Invoice.CustomerID

Don't forget the help files, there are lots of examples for this stuff

[rusdyrip]

SELECT InvoiceID,CustomerID,Customer,Name,SalesID,SalesName,Quantity
FROM Person
Join Invoice on Person.ID=Invoice.CustomerID

Don't forget the help files, there are lots of examples for this stuff

the code don't make it work
i need to show the salesname,and the field 'salesname' is on Person Table same like 'customername' it's on 1 field

it's look like this
Table Invoice = Invoice.SalesID and Invoice.Custome rID

Table Person= Person.PersonID ,Person.PersonN ame,Person.Pers onTipe

so first i must join the invoice with person table with Invoice.Custome rID=Person.Pers onID

but how to join them again with
Invoice.SalesID =Person.PersonI D


thx

[ck9663]

you only mentioned 2 tables on your first post. on your latest reply, it looks like it's coming from four tables. would you mind posting the name and structure of all the four tables? and how you would like them joined?

-- CK

[rusdyrip]

you only mentioned 2 tables on your first post. on your latest reply, it looks like it's coming from four tables. would you mind posting the name and structure of all the four tables? and how you would like them joined?

-- CK

sory bad explanation

this is my table structure,i only have 2 table

Table Person
IDPERSON,TYPE,N AME
----------------------------------------
001,CUSTOMER,AN DRE
002,SALES,MARK

Table Invoice
INVNO,IDCUSTOME R,IDSALES
-----------------------------------------------
INV-01,001,002

JOIN TABLE - i want to make this
INVNO,IDCUSTOME R,NAME,IDSALES, NAME
---------------------------------------------------
INV-001,001,ANDRE,0 02,MARK

i hope this will make it clear

[ck9663]

sory bad explanation

this is my table structure,i only have 2 table

Table Person
IDPERSON,TYPE,N AME
----------------------------------------
001,CUSTOMER,AN DRE
002,SALES,MARK

Table Invoice
INVNO,IDCUSTOME R,IDSALES
-----------------------------------------------
INV-01,001,002

JOIN TABLE - i want to make this
INVNO,IDCUSTOME R,NAME,IDSALES, NAME
---------------------------------------------------
INV-001,001,ANDRE,0 02,MARK

i hope this will make it clear

try:

select INVNO, CustomerTable.Name as CustomerName, SalesTable.Name as SalesManName
from INVOICE
inner join 
(select IDPERSON, TYPE, NAME from TablePerson where TYPE = 'CUSTOMER') CustomerTable on CustomerTable.IDPERSON = INVOICE.IDCUSTOMER
inner join
(select IDPERSON, TYPE, NAME from TablePerson where TYPE = 'SALES') SalesTable on SalesTable.IDPERSON = INVOICE.IDSALES

the query assumes all IDs in invoice table are in person table. if there are IDs in invoice table that are not in persons table, change INNER to LEFT OUTER

-- ck

[rusdyrip]

try:

select INVNO, CustomerTable.Name as CustomerName, SalesTable.Name as SalesManName
from INVOICE
inner join 
(select IDPERSON, TYPE, NAME from TablePerson where TYPE = 'CUSTOMER') CustomerTable on CustomerTable.IDPERSON = INVOICE.IDCUSTOMER
inner join
(select IDPERSON, TYPE, NAME from TablePerson where TYPE = 'SALES') SalesTable on SalesTable.IDPERSON = INVOICE.IDSALES

the query assumes all IDs in invoice table are in person table. if there are IDs in invoice table that are not in persons table, change INNER to LEFT OUTER

-- ck

is there any other syntax?
it seems my sql editor can't execute the script
i am using firebird maestro.

(select IDPERSON, TYPE, NAME from TablePerson where TYPE = 'CUSTOMER')

the error refer to this code

thx

[ck9663]

is there any other syntax?
it seems my sql editor can't execute the script
i am using firebird maestro.

(select IDPERSON, TYPE, NAME from TablePerson where TYPE = 'CUSTOMER')

the error refer to this code

thx

try enclosing the TYPE field with hard brackets...make it [TYPE]....i think it's a reserve word...if you run that query only, does it run? what's the actual error?

-- ck

[rusdyrip]

try enclosing the TYPE field with hard brackets...make it [TYPE]....i think it's a reserve word...if you run that query only, does it run? what's the actual error?

-- ck

i have change the TYPE to [TYPE]
but still error
it say :

SQL Error: Dynamic SQL Error SQL error code = -104 Token unknown - line 4, char 2
Error Code: -104. Invalid token The SQL

it's focus on (select.....

[ck9663]

what version of sql server are you using?

-- ck

[rusdyrip]

what version of sql server are you using?

-- ck

i am using firebird sql server, is that different?
i think the sytax is same

[gpl]

I am assuming that the PK of the Person table is ID (and not ID, Type)

Select 
	i.INVNO,
	c.ID,
	c.Name,
	s.ID,
	s.Name
From Invoice i 
Inner Join Person c
	On i.IDCUSTOMER = c.ID
Inner Join Person s
	On i.IDSALES = s.ID

Graham

[ck9663]

I am assuming that the PK of the Person table is ID (and not ID, Type)

Select 
	i.INVNO,
	c.ID,
	c.Name,
	s.ID,
	s.Name
From Invoice i 
Inner Join Person c
	On i.IDCUSTOMER = c.ID
Inner Join Person s
	On i.IDSALES = s.ID

Graham

it looks like my query will not work with kind of sql server. but i think Graham's will....

and just in case PK is ID, TYPE....try:


Select
i.INVNO,
c.ID,
c.Name,
s.ID,
s.Name
From Invoice i
Inner Join Person c
On i.IDCUSTOMER = c.ID and c.TYPE = 'CUSTOMER'
Inner Join Person s
On i.IDSALES = s.ID and s.TYPE = 'SALES'

-- ck

[rusdyrip]

it looks like my query will not work with kind of sql server. but i think Graham's will....

and just in case PK is ID, TYPE....try:


Select
i.INVNO,
c.ID,
c.Name,
s.ID,
s.Name
From Invoice i
Inner Join Person c
On i.IDCUSTOMER = c.ID and c.TYPE = 'CUSTOMER'
Inner Join Person s
On i.IDSALES = s.ID and s.TYPE = 'SALES'

-- ck

it's works great lol
thank you all,