Complex join

[David Richards]

Hi,

I'm having a problem linking 4 tables in MYSQL. The first table contains
customer details, the next item details and the next 2 contain sales
information. The final output of the query should be:-

CustomerName Item Sales2002 Sales2003

(customers.name) (items.inum) (sales2002.val) (sales2003.val)

Test One ABM10 100 200

Test One ABM11 200 300

Test Three RJM10 200 400

Test Three SLP02 800 50

The table structures are as follows:-

Customers

ID Unique Record identifier

Name Customer Name

Items

ID Unique Record identifier

INUM Item Number

DESC Description

SALES2003 and SALES2002

ID Unique Record identifier

PID Part number ID

CID Customer ID

VAL Sales Value

I am new to SQL and have tried a number of different joins but I always seem
to get duplicate data. If anyone is able to tell me what type of join I need
to use or perhaps know of a way to format these tables in the required
layout that would be great.

Thanks in advance

Dave

[David L]

i'm not a good answerer but since no one has had a go...

if it could be assummed that one sales tables covers all customers/items
that the other sales tables has, then from this "bigger" table do outer
joins to the customer table, the items table, and the other sales table.
When joining to the other sales table, need to involve both the
customer id and the item id.

hope that makes some sense.
David Richards wrote:

Hi,

I'm having a problem linking 4 tables in MYSQL. The first table contains
customer details, the next item details and the next 2 contain sales
information. The final output of the query should be:-

CustomerName Item Sales2002 Sales2003

(customers.name) (items.inum) (sales2002.val) (sales2003.val)

Test One ABM10 100 200

Test One ABM11 200 300

Test Three RJM10 200 400

Test Three SLP02 800 50

The table structures are as follows:-

Customers

ID Unique Record identifier

Name Customer Name

Items

ID Unique Record identifier

INUM Item Number

DESC Description

SALES2003 and SALES2002

ID Unique Record identifier

PID Part number ID

CID Customer ID

VAL Sales Value

I am new to SQL and have tried a number of different joins but I always seem
to get duplicate data. If anyone is able to tell me what type of join I need
to use or perhaps know of a way to format these tables in the required
layout that would be great.

Thanks in advance

Dave

[David Richards]

I'll give it a go.

Thanks for the reply.
Dave
"David Richards" <da**@tools.co.uk> wrote in message
news:ca**********@hercules.btinternet.com...

Hi,

I'm having a problem linking 4 tables in MYSQL. The first table contains
customer details, the next item details and the next 2 contain sales
information. The final output of the query should be:-

CustomerName Item Sales2002 Sales2003

(customers.name) (items.inum) (sales2002.val) (sales2003.val)

Test One ABM10 100 200

Test One ABM11 200 300

Test Three RJM10 200 400

Test Three SLP02 800 50

The table structures are as follows:-

Customers

ID Unique Record identifier

Name Customer Name

Items

ID Unique Record identifier

INUM Item Number

DESC Description

SALES2003 and SALES2002

ID Unique Record identifier

PID Part number ID

CID Customer ID

VAL Sales Value

I am new to SQL and have tried a number of different joins but I always seem to get duplicate data. If anyone is able to tell me what type of join I need to use or perhaps know of a way to format these tables in the required
layout that would be great.

Thanks in advance

Dave