Sales Query - Please help!!

[coccoster]

Hi guys,



Am having some trouble coming up with a query.

I have to get data across 3 tables.


Table 1: SALES

SALE_ID	SALE_DATE	CUSTID	PAID	SALES_PERSON_ID	PAYMENT_TYPE
100001	01-Aug-12	20900	Y	SP0001	CASH
100002	03-Aug-12	20766	Y	SP0001	CREDIT

Table 2: SALES_LINE2

ID	SALE_ID	PRODUCT_ID	QTY
1	100000	P100021	3
2	100001	P100068	4

Table 3:

PRODUCT_ID	DESCRIPTION	CATEGORY_ID	MANUFACTURER_ID	UNIT_PRICE
P100001	Telehook	DMS	ATDEC	359
P100068	Visidec	NBAC	ATDEC	199

The desired output should be:

Sales Person ID:	SP001		Name	John	Smith
Sale ID	Product ID	Sale Date	Description	Category	Price	Quantity
10001	P100068	01-Aug-12	Visidec	NBAC	199	4
10000	P100021	03-Aug-12	Telehook	DMS	359	3
Total	For		Sales	Person:	558

What i currently have is:

SELECT B.SALE_ID, B.PRODUCT_ID, A.SALE_DATE, C.DESCRIPTION, CATEGORY_ID, UNIT_PRICE, B.QTY, A.SALES_PERSON_ID
FROM SALES AS A, SALES_LINE AS B, PRODUCT AS C
WHERE B.SALE_ID = A.SALE_ID
AND B.PRODUCT_ID = B.PRODUCT_ID
ORDER BY A.SALES_PERSON_ID;


I know that isn't quite right though but just can't get my head around how to fix it.

Thanks in advanced for your help.

[rpeare]

So you want to see all the information related to each sale.

Add your SALES table to the query
Add the fields from the SALES tables you want
Add the SALES_LINE2 table to the query
Create a link between SALES and SALES_LINE2
Double Click the line between them, select the option that makes the arrow point FROM the SALES table TO the SALES_LINE2 table
Add the fields from the SALES_LINE2 table you want
Add the PRODUCT table
Create a link between SALES_LINE2 and PRODUCT
Double Click the line between them, select the option that makes the arrow point FROM the SALES_LINE2 table TO the PRODUCT table
Add the fields from the PRODUCT table you want

This should give you the information you want

The remainder of what you want should be done in a report, not in a query