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
13 1472 -
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
-
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.CustomerID
Table Person= Person.PersonID,Person.PersonName,Person.PersonTip e
so first i must join the invoice with person table with Invoice.CustomerID=Person.PersonID
but how to join them again with
Invoice.SalesID=Person.PersonID
thx
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
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,NAME
----------------------------------------
001,CUSTOMER,ANDRE
002,SALES,MARK
Table Invoice
INVNO,IDCUSTOMER,IDSALES
-----------------------------------------------
INV-01,001,002
JOIN TABLE - i want to make this
INVNO,IDCUSTOMER,NAME,IDSALES,NAME
---------------------------------------------------
INV-001,001,ANDRE,002,MARK
i hope this will make it clear
sory bad explanation
this is my table structure,i only have 2 table
Table Person
IDPERSON,TYPE,NAME
----------------------------------------
001,CUSTOMER,ANDRE
002,SALES,MARK
Table Invoice
INVNO,IDCUSTOMER,IDSALES
-----------------------------------------------
INV-01,001,002
JOIN TABLE - i want to make this
INVNO,IDCUSTOMER,NAME,IDSALES,NAME
---------------------------------------------------
INV-001,001,ANDRE,002,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
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
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
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.....
what version of sql server are you using?
-- ck
what version of sql server are you using?
-- ck
i am using firebird sql server, is that different?
i think the sytax is same
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
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
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,
Sign in to post your reply or Sign up for a free account.
Similar topics
by: netpurpose |
last post by:
I need to extract data from this table to find the lowest prices of
each product as of today. The product will be listed/grouped by the
name only, discarding the product code - I use...
|
by: Prem |
last post by:
Hi,
I am having many problems with inner join. my first problem is :
1) I want to know the precedance while evaluating query with multiple
joins.
eg.
select Employees.FirstName,...
|
by: stu_gots |
last post by:
I have been losing sleep over this puzzle, and I'm convinced my train
of thought is heading in the wrong direction. It is difficult to
explain my circumstances, so I will present an identical...
|
by: jimh |
last post by:
I'm not a SQL expert. I want to be able to write a stored procedure
that will return 'people who bought this product also bought this...'.
I have a user table that links to a transaction table...
|
by: Steve |
last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
"Left Outer Joins" in order to return every transaction for a specific
set of criteria.
Using three "Left Outer Joins"...
|
by: Dom Boyce |
last post by:
Hi
First up, I am using MS Access 2002.
I have a database which records analyst rating changes for a list of
companies on a daily basis. Unfortunately, the database has been set
up (by my...
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a...
|
by: Arvin Portlock |
last post by:
I can't come up with a query that works. Can anyone help?
Conceptually the relationships are easy to describe. I have
a table for books (Entries), a table for authors (Authors),
and a linking...
|
by: davidsavill |
last post by:
Hi All,
I am migrating a database from Firebird/Interbase to DB2 and have having issues with the stored procedures/functions.
I have a number of functions that loop over a FOR loop, each pass...
|
by: LanaR |
last post by:
Hello,
one sql statement is causing severe performance issue. The problem occurs only in UDB environment, the same statemnt on the mainframe is running fine. I have an explain output from the sql....
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |