Joining text fields in Base [closed]

asked 2014-01-07 12:44:00 +0100

The Cougar Kid gravatar image

I am trying to convert an ACCESS database to BASE; I have used ACCESS for many years, but this is my first try with Base. One key thing I need to do is a simple join on two tables - one of them in fact a view - on a text field - the person's name in fact. I am doing this in SQL with this query: SELECT "results"."date", "concatenate names"."id", "results"."score", "results"."section" FROM "concatenate names", "results" WHERE "concatenate names"."name" = "results"."player1" The entries in the 'player1' field in the 'results' table comprise 21 different values, all of which are present amongst the 300 entries in the 'name' field in 'concatenate names' yet this query produces zero rows; the spellings, spacings, etc are identical. Why? The database operates around the (numeric) 'id' field (as in 'concatenate names') but unfortunately this value is not available in the 'results' table, which comes from an external source.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2016-02-18 11:21:13.889227


Did you use the default settings for creating a new database to test your example? That would mean HSQLDB is the database engine and it has some limitations. Not sure if your problem is caused by one of them (yet).

bencomp gravatar imagebencomp ( 2014-01-08 00:54:18 +0100 )edit

Could you be so kind as to change your main question to "How do I join tables on text fields in Base?" (it's mostly about 'join on text fields' instead of 'joining text fields'). Thanks.

bencomp gravatar imagebencomp ( 2014-01-08 00:59:59 +0100 )edit

Have you tried using JOINS? Does your player 1 column function as the results table's primary key? (i.e. confirm only 21 rows, each with a unique player 1 value). May we assume the names may be repeated in the concatenate names table?

frofa gravatar imagefrofa ( 2014-01-10 00:03:43 +0100 )edit