The error is not in the join, but in the fact that you have two invoices with the same invocecode. The items are retrieved and displayed for both invoices.
If this is correct, select distinct should solve your problem. On Mon, Jan 12, 2009 at 5:59 PM, Ron <r...@silverbackasp.com> wrote: > 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 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be > > -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED.
