List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:January 12 2009 5:11pm
Subject:Re: help on join
View as plain text  
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 <ron@stripped> 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=1
>
>


-- 
Celsius is based on water temperature.
Fahrenheit is based on alcohol temperature.
Ergo, Fahrenheit is better than Celsius. QED.

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