List:General Discussion« Previous MessageNext Message »
From:Ron Date:January 12 2009 4:59pm
Subject:help on join
View as plain text  
Hi All,

I got the following tables:

table items

+-------------+-------------+------------+-------------+----------+----------------------+----------------------+
| accountcode | invoicecode | invitemqty | packagecode | itemcode | 
packagename          | packagedesc          |
+-------------+-------------+------------+-------------+----------+----------------------+----------------------+
| 103         |  2009011301 |          1 |           1 |        0 | 
Closed Trial Package | Closed Trial Package |
| 103         |  2009011301 |          1 |           1 |        0 | 
carryover            | Previous Balance     |
+-------------+-------------+------------+-------------+----------+----------------------+----------------------+

table invoice

+-------------+----------------+--------+-------------+-----------------+---------------+------------+
| accountcode | refno          | status | invoicecode | invoicedatefrom 
| invoicedateto | billdate   |
+-------------+----------------+--------+-------------+-----------------+---------------+------------+
| 103         | 103A2009011301 | unpaid |  2009011301 | 2008-12-13 
| 2009-01-12    | 2009-01-13 |
| 103         | 103A2009011301 | unpaid |  2009011301 | 2008-12-08 
| 2008-12-13    | 2009-01-13 |
+-------------+----------------+--------+-------------+-----------------+---------------+------------+

select * from invoice join items on invoice.invoicecode = 
items.invoicecode where invoice.accountcode='103';

+-------------+----------------+--------+-------------+-----------------+---------------+------------+-------------+-------------+------------+-------------+----------+----------------------+----------------------+
| accountcode | refno          | status | invoicecode | invoicedatefrom 
| invoicedateto | billdate   | accountcode | invoicecode | invitemqty | 
packagecode | itemcode | packagename          | packagedesc          |
+-------------+----------------+--------+-------------+-----------------+---------------+------------+-------------+-------------+------------+-------------+----------+----------------------+----------------------+
| 103         | 103A2009011301 | unpaid |  2009011301 | 2008-12-13 
| 2009-01-12    | 2009-01-13 | 103         |  2009011301 |          1 | 
           1 |        0 | Closed Trial Package | Closed Trial Package |
| 103         | 103A2009011301 | unpaid |  2009011301 | 2008-12-08 
| 2008-12-13    | 2009-01-13 | 103         |  2009011301 |          1 | 
           1 |        0 | Closed Trial Package | Closed Trial Package |
| 103         | 103A2009011301 | unpaid |  2009011301 | 2008-12-13 
| 2009-01-12    | 2009-01-13 | 103         |  2009011301 |          1 | 
           1 |        0 | carryover            | Previous Balance     |
| 103         | 103A2009011301 | unpaid |  2009011301 | 2008-12-08 
| 2008-12-13    | 2009-01-13 | 103         |  2009011301 |          1 | 
           1 |        0 | carryover            | Previous Balance     |
+-------------+----------------+--------+-------------+-----------------+---------------+------------+-------------+-------------+------------+-------------+----------+----------------------+----------------------+

what's was my mistake on the join why it resulted to four rows 
(duplicate results) ? how can make it that the result is without 
duplicate, which in this case should be 2 rows. TIA.

Regards

Ron
Thread
help on joinRon12 Jan
  • Re: help on joinJohan De Meersman12 Jan