Trust ID PBCEX logo 300x102

Inner joint

7 years 11 months ago
Mauricio Florez
Expert Boarder
Expert Boarder
Posts: 87
More
Topic Author
Inner joint #3664
Hi Rutger:

We would like to ask how can we merge or do a inner joint based on a foreign key on two diferent tables? we are using SQL standard and CE 6.5 ultimate.

The end customer has the employee name on a database table, but their title inside the company are in another table. But tables has a relationship using the title code.

That was done because that way they don't have to write the same title to the lot of people who have the same title within the company , just the code. But as the user wants to print the company title, we have to use that code (number) to go to the second table and retrieve the title like a text.

Waiting for your replay. Regards,

Mauricio F.
7 years 11 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Inner joint #3666
Hi Mauricio,

Joints are not supported in CardExchange, but you can get them anywhere in Amsterdam ;) . Inner joins based on a foreign key other than the field you defined as primary keys in CardExchange should be define in a view in the database (preferred way) or in an internal view in CardExchange if it is organizationally impossible to define a view in the database.

If the database needs to be edited in the database-edit window too, you should not show the field from the other table, but you can show the foreign-key field and define it in the database setup as a drop-down menu based on a database table. That way, you can show the readable field from the lookup table in CardExchange and still store the foreign-key value in the base table.

regards,
Rutger.

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)
7 years 11 months ago
JHicks
Moderator
Moderator
Posts: 64
More
Inner joint #3676
We have the same issue with MS access.
We wanted 10 MS Excel files to be all looked up at 1 time.
So we created 10 linked tables in MS Access, and then put them into a joint query. (but CardExchange does not support joint query)
The workaround is to create the joint query in MS access, then copy the SQL created by the join query to the <internal view> option in CardExchange.
When we pasted the SQL we needed to remove the last semi colon to get it to work.
We also had problems if all the field names were not identical in all the excel spreadsheets.
It works perfectly after that.

Jason Hicks MBA
National Service & Projects Manager
[Practical Peripherals Corporation Pty Ltd (Australia) http://www.ppc.com.au ]
"No we don't really eat much Kangaroo meat here, Cows are much easier to catch"
The following user(s) said Thank You Rutger Koperdraad