1

I'm pretty new to OpenOfffice Base, but not to SQL.

I want to insert into a table the results of a join. It works in the simple case: 

insert into "tbl1" ("last_name", "first_name") 
select "last_name", "first_name" from "contacts"

but NOT with 

insert into "tbl1" ("last_name", "first_name") 
select "contacts.last_name", "contact2.first_name"
from "contacts" INNER JOIN "contact2" 
    on "contacts.last_name" = "contact2.last_name" 
    and "contacts.first_name" = "contact2.first_name"

I get this error: 

1: Column not found: contacts.last_name in statement 
[insert into "tbl1" ("last_name", "first_name") select 
"contacts.last_name", "contact2.first_name" from "contacts" 
inner join "contact2" on "contacts.last_name" = "contact2.last_name"
and "contacts.first_name" = "contact2.first_name"]

But contacts.last_name exists and is populated with data.

I've checked docs and don't see it, so I hope someone provide what should be a simple answer. Also I'd like to know how I can write the results (instead of the insert) to a csv file. The INTO TEXT clause objects to the join, I think.

Thanks.

Jose Ricardo Bustos M.
  • 8,016
  • 6
  • 40
  • 62
Buz
  • 11
  • 2

1 Answers1

1

You need an additional set of double quotes on every two-part column reference - the table name and column name get quoted individually "contacts"."last_name"

Edit: The way I know of to get a CSV file of a query is to import the query results into Calc and then save the Calc file as CSV. See this guide on importing Base queries (or tables) into Calc: [Tutorial] Using registered datasources in Calc

Lyrl
  • 925
  • 6
  • 16