To perform a self-join you just have to alias one or both tables.
In your case, you want to join on ( I guess) the SLM + CUST + ITEM,
restricting the tables to, respectively, 2003 and 2002. Further, you
want to filter out any rows where there's a match in 2002.
I believe the following does it:
select s03.* , s02.id
from sales s03 left outer join sales s02
on s03.item = s02.item and s03.slm = s02.slm and s03.cust =
s02.cust
and year(s02.date) = 2002
WHERE year(s03.date) = 2003
and s02.id is null;
> -----Original Message-----
> From: Charles Haven [mailto:[EMAIL PROTECTED]
> Sent: Monday, July 14, 2003 8:30 AM
> To: [EMAIL PROTECTED]
> Subject: LEFT SELF Join -- LEFT join on same table
>
>
> Hello all,
>
> Is it possible to LEFT JOIN a table to itself?
>
> I want to see all records in a table where the year of the
> sales date is
> 2003 and where the salesman sold an item to a customer to
> which he did not
> sell that item to in 2002.
>
> The following is an example table (SALES):
>
> +----+-----+------+------------+--------+------+-----+
> | ID | SLM | CUST | DATE | ITEM | COST | QTY |
> +----+-----+------+------------+--------+------+-----+
> | 1 | 1 | AAA | 2002-01-01 | APPLE | 1.00 | 2 |
> | 2 | 1 | BBB | 2002-01-01 | APPLE | 1.00 | 2 |
> | 3 | 1 | CCC | 2002-01-01 | PEAR | 1.00 | 2 |
> | 4 | 1 | AAA | 2002-01-01 | PEAR | 1.00 | 2 |
> | 5 | 2 | AAA | 2002-01-01 | APPLE | 1.00 | 2 |
> | 6 | 3 | CCC | 2002-01-01 | BANANA | 1.00 | 2 |
> | 7 | 1 | AAA | 2003-01-01 | APPLE | 1.00 | 2 |
> | 8 | 2 | AAA | 2003-01-01 | APPLE | 1.00 | 2 |
> | 9 | 3 | CCC | 2003-01-01 | BANANA | 1.00 | 2 |
> | 10 | 1 | AAA | 2003-01-01 | BANANA | 1.00 | 2 |
> | 11 | 3 | CCC | 2003-01-01 | APPLE | 1.00 | 2 |
> +----+-----+------+------------+--------+------+-----+
>
> What I want to see in my result is that in 2003 Salesman 1
> sold bananas to
> customer AAA and salesman 3 sold apples to customer CCC. I
> want to see
> these because these salesmen did not sell these customers
> these items in 2002.
>
> Thanks,
> Charles
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
