SQL QUERY help needed

Forum Moderators: open

Message Too Old, No Replies

SQL QUERY help needed

jecasc

10:23 am on Mar 10, 2007 (gmt 0)

I am stuck with an SQL Query. I have two tables. In one table I have all customers with their addresses, in the other I have all orders. Now I need an SQL Query to return all customers that have ordered in the last three month.

This is what I came up with so far:

SELECT * FROM customers c JOIN orders o ON (c.addr_id = o.rec_id) WHERE o.order_date >='" . $date ."'"

The query is working so far, however if a customer has ordered twice in the last three month, the query returns the customer address twice also. But I need the address only once.

wheelie34

2:13 pm on Mar 10, 2007 (gmt 0)

Hi
Have you tried select distinct

jecasc

2:41 pm on Mar 10, 2007 (gmt 0)

Haha. Of course! Stupid me. That did the trick. Thanks.

syber

1:49 pm on Mar 11, 2007 (gmt 0)

A better way to avoid the duplicate row problem is to use a subquery instead of a JOIN.


SELECT * FROM customers 
WHERE addr_id IN (SELECT rec_id FROM orders 
      WHERE order_date >='" . $date ."'")