Joining from two different tables

Hi all,
I have a test sales table with 12 records showing sale_id, sale_date, prod_i
d, cust_id, unit_price, qty. Then I have two other tables, customer and pro
ducts from where I would like to extract product description and customer na
me. When joining the sales the table with either customer or product, I obta
in the 12 original records with the desired description (product or customer
). However, when attempting to obtain both descriptions, 2 records are left
out. Here is the first query:
SELECT sales.date, sales.cust_id, products.prod_desc, sales.unit_price, sale
s.qty
FROM sales s INNER JOIN products p ON s.prod_id = p.prod_id
The problem is when the following is attempted:
SELECT sales.date, customers.cust_desc, products.prod_desc, sales.unit_price
, sales.qty
FROM customers c INNER JOIN (sales s INNER JOIN products p ON s.prod_id = p.
prod_id) ON c.cust_id = s.cust_id
What am I missing? Thanks,give this a try...
SELECT sales.date, customers.cust_desc, products.prod_desc,
sales.unit_price, sales.qty
FROM customers c INNER JOIN (sales s INNER JOIN products p ON s.prod_id
= p.prod_id) x ON c.cust_id = x.cust_id
also do all sales have all products if not then you have to do a left join
on the sales table
"itmex" <itmex.1mhd5p@.mail.codecomments.com> wrote in message
news:itmex.1mhd5p@.mail.codecomments.com...
> Hi all,
> I have a test sales table with 12 records showing sale_id, sale_date,
> prod_id, cust_id, unit_price, qty. Then I have two other tables,
> customer and products from where I would like to extract product
> description and customer name. When joining the sales the table with
> either customer or product, I obtain the 12 original records with the
> desired description (product or customer). However, when attempting to
> obtain both descriptions, 2 records are left out. Here is the first
> query:
> SELECT sales.date, sales.cust_id, products.prod_desc, sales.unit_price,
> sales.qty
> FROM sales s INNER JOIN products p ON s.prod_id = p.prod_id
>
> The problem is when the following is attempted:
> SELECT sales.date, customers.cust_desc, products.prod_desc,
> sales.unit_price, sales.qty
> FROM customers c INNER JOIN (sales s INNER JOIN products p ON s.prod_id
> = p.prod_id) ON c.cust_id = s.cust_id
> What am I missing? Thanks,
>
> --
> itmex
> ---
> Posted via http://www.codecomments.com
> ---
>|||This is same as what you had, and should work, unless there are some sales
records with cust_id not in Customers table...
SELECT S.date, S.cust_id, P.prod_desc,
S.unit_price,S.qty
FROM Sales S
Join Products P
On P.prod_id = S.prod_id
Join Customers C
On C.cust_id = S.cust_id
If above does not work, please post the actual data, in all three tables,
and the "wrong" output of the query (from which you believe two records are
missing)
-- oh and specifiy Which two records you think are "missing". (SHould be in
there )
"itmex" wrote:

> Hi all,
> I have a test sales table with 12 records showing sale_id, sale_date,
> prod_id, cust_id, unit_price, qty. Then I have two other tables,
> customer and products from where I would like to extract product
> description and customer name. When joining the sales the table with
> either customer or product, I obtain the 12 original records with the
> desired description (product or customer). However, when attempting to
> obtain both descriptions, 2 records are left out. Here is the first
> query:
> SELECT sales.date, sales.cust_id, products.prod_desc, sales.unit_price,
> sales.qty
> FROM sales s INNER JOIN products p ON s.prod_id = p.prod_id
>
> The problem is when the following is attempted:
> SELECT sales.date, customers.cust_desc, products.prod_desc,
> sales.unit_price, sales.qty
> FROM customers c INNER JOIN (sales s INNER JOIN products p ON s.prod_id
> = p.prod_id) ON c.cust_id = s.cust_id
> What am I missing? Thanks,
>
> --
> itmex
> ---
> Posted via http://www.codecomments.com
> ---
>|||Why did you use parens and the infixed join syntax? The most this can
do is force an order of execution if you do not have a smart optimizer.
And you have to use the alias table names in the SELECT clause:
SELECT S.date, C.cust_desc, P.prod_desc, S.unit_price, S.qty
FROM Customers AS C,
Sales AS S,
Products AS P
WHERE S.prod_id = P.prod_id
AND C.cust_id = S.cust_id;
This should work unless you have no DRI between Sales and Products, so
people can sell stuff you don't stock. Likewise, sales to people who do
not exist will be a another problem.
DDL would have shown us that, which is why it is the netiquette here
to post it.
Add the DRI actions you need after you do a data audit.