[Resolved] SQL server query returning duplicated data

Spames

let's say I have 4 tables...

orders:
order_id
product_id
customer_id
quantity

customers:
cutomer_id
name
phone
email

customer_addresses:
customer_id
address1
address2
etc.....

products:
product_id
description
price

When I join the tables to display an order, the order is duplicated as many times as there are addresses for each customer. What I would like to get is one order, and the first customer address. Not the same order 5 times with each of the customers addresses.

Here's what my query is now. I've tried selecting DISTINCT order_id, and it still gives me the duplicates.

SELECT (not really, just using the for this example...)
FROM orders
INNER JOIN products on orders.product_id = products.product_id
INNER JOIN customers ON orders.customer_id = customers.customer_id
*INNER JOIN customer_addresses ON customers.customer_id = customer_addresses.customer_id

I've also tried LEFT OUTER JOIN and that doesn't do it either...

Can someone help before I lose my mind?

Thanks

Spames

Ok, I figured out I can do it by collecting the list of orders for the customer, then running a loop for each order ID to run the query like this:

SELECT TOP 1 *
FROM orders
INNER JOIN products on orders.product_id = products.product_id
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN customer_addresses ON customers.customer_id = customer_addresses.customer_id
WHERE orders.order_id = order number

Is there another way to do it?

Lars_Berg

How do you know which adress is the first? I'll assume that there is a sequence
number.

SELECT <columns of interest>, (select adress from customer_adresses 
where customer_id = customer.customer_id
and ordinal = 1)
FROM orders
INNER JOIN products on orders.product_id = products.product_id
INNER JOIN customers ON orders.customer_id = customers.customer_id

Spames

That works! Thanks-