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]

Re: Complex join

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:
[color=blue]
> 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
>
>
>
>
>
>[/color]

[David Richards]

Re: Complex join

I'll give it a go.

Thanks for the reply.
Dave
"David Richards" <dave@tools.co. uk> wrote in message
news:ca1d1c$sq3 $1@hercules.bti nternet.com...[color=blue]
> 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[/color]
seem[color=blue]
> to get duplicate data. If anyone is able to tell me what type of join I[/color]
need[color=blue]
> to use or perhaps know of a way to format these tables in the required
> layout that would be great.
>
>
>
> Thanks in advance
>
> Dave
>
>
>
>
>
>[/color]