Hi Let me take a stab at it. I don't think you need a join at all. Check the syntax but I think you can do it like this.
SELECT *,concat(slm,item,cust,year) as result1,concat(slm,item,cust,2003) as result2 from sales where result1!=result2 AND date>2001 year would be taken from your date field in the table Mike ----- Original Message ----- From: "Charles Haven" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: July 14, 2003 11:29 AM 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/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
