Re: [PHP] Can't postgres join tables on varchar fields ?

Gurudutt,

> Actually I have two tables
>
> 1. InvoiceTab
> 2. CollectionTab
>
> InvoiceTab has CustCode(Customer Code) Integer & InvCode(Invoice
> Number) Varchar
> combination as primary key
>
> CollectionTab has CollCode(Collection Code)Integer as Primary key.
> CollectionTab has InvCode as well!!
>
> For any InvCode there may be more than one entry in the collection
> table.
>
>
> Now if I join the tables based on  InvCode, I get just four rows,
> where as, if I run the same query in mysql I get 1491 records, there
> are
> about 1491 collection entries in database.
>
> Both pgsql form of database and mysql database have the same set of
> records. I just did a cross check running the query on both the forms
> of database.
>
> Query is :
> ----------
>
> select ACT_CollectionTab.*,ACT_InvoiceTab.IncrCode
> from ACT_InvoiceTab,ACT_CollectionTab
> where ACT_InvoiceTab.InvCode=ACT_CollectionTab.InvCode
>
> Can u suggest me why this is happening!!

Three possibilities:
1. Your upper/lower case is screwed up between CollectionTab and
InvoiceTab; that is, CollectionTab has upper case InvCodes and
InvoiceTab has lower case, or vice-versa.

2. You have accidentally defined InvCode as VARCHAR in one table and
CHAR in another.  If the InvCodes are of variable length, this would
cause failure to match because of the space-filling inherent in CHAR.
Similarly, defining the fields as different-length CHAR fields would
cause this problem.

3. Some error you or your scripts made in converting the data caused
trailing spaces to be appended to the InvCode field in one table but not
the other.

To really determine the problem, I would need to see:

1. The actual PostgreSQL tabledefs (pg_dump -s -t "InvoiceTab" database)

2. The first 10 rows of data for each table.  Please cut-and-past and do
not transcribe the data, because you almost certainly have a trailing
space, case, or punctuation problem here and only an exact dump will
show the problem.

-Josh Berkus



______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco