How to add data from different tables

[lonk]

We have variety of customers:

CustomerCode	CustomerName	CustomerAddress1	CustomerAddress2
A	Mr. A	London	UK
B	Company B	Bournemouth	UK
C	Company C	Leeds	UK

Some customers are companies and have branches:

CompanyCode	BranchCode	BranchAddress1	BranchAddress2
B	1	Bangkok	Thailand
B	2	Jakarta	Indonesia
B	3	Manila	The Philipines
C	1	New York	USA

I need a simple address book/query as shown hereunder and headquarters and all branches address in the same columns:

CustomerCode	CustomerName	BranchCode	Address1	Address2
A	Mr. A		London	UK
B	Company B		Bournemouth	UK
B	Company B	1	Bangkok	Thailand
B	Company B	2	Jakarta	Indonesia
B	Company B	3	Manila	The Philipines
C	Company C		Leeds	UK
C	Company C	1	New York	USA

Is there any way of doing this using a query in Base/Firebird?

Firebird0041.odb (4.2 KB)

LibreOffice:
Version: 7.3.7.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.7-0ubuntu0.22.04.1
Calc: threaded
Base: Firebird
OS:
Ubuntu 22.04

[cpb]

select
	"CustomerCode", "CustomerName", '' "BranchCode", "CustomerAddress1" "Address1", "CustomerAddress2" "Address2"
from
	"Customer"
union all
	select
		"CompanyCode", 'Company ' || "CompanyCode", "BranchCode", "BranchAddress1", "BranchAddress2"
	from
		"Branch"
order by 1, 3

[lonk]

Can you please help?
There was an error message:

[Villeroy]

You have to run this in direct SQL mode.

[lonk]

How can I edit and use this direct SQL as an SQL statement in Macro ?

[Villeroy]

You are the programmer.

[cpb]

the thread as asked has been answered, now you ask a different question.
.

@lonk

How can I edit and use this direct SQL as an SQL statement in Macro ?

I don’t understand what the above means, you provide zero information.
how and from where is the macro fired? where is the result to be targeted? what exactly do you need to edit?
.
a UNION query requires direct mode.
the code can be executed via macro without issue.
you know how to double quote table/field names and then execute the SQL string using the STATEMENT service.
it’s also just as easy to execute a saved/stored query.
.
if you save the UNION query as “qMyQuery” using direct mode then this code executes that query and shows output via msgbox.

option explicit
Sub RunQuery
	'STORE IN DB, RUN FROM IDE
	dim oController as object, oCon as object, oStatement as object, oDataSource as object
	dim oQueryDefinitions as object, oQuery as object, oResult as object
	dim sSQL as string, sSpc as string, sTemp as string
	dim i as integer
	
	oController = thisdatabasedocument.currentcontroller
	if not oController.isconnected then oController.connect
	oCon = oController.activeconnection
	oStatement = oCon.createstatement
	
	oDataSource = thisdatabasedocument.datasource
	oQueryDefinitions = oDataSource.querydefinitions
	oQuery = oQueryDefinitions.getbyname("qMyQuery")
	sSQL = oQuery.command

	sSpc = space(5)
	oResult = oStatement.executequery(sSQL)
	for i = 0 to oResult.columns.count - 1
		sTemp = sTemp & oResult.columns.getbyindex(i).label & sSpc
	next
	sTemp = sTemp & chr(10)	
	sTemp = sTemp & "--------------------------------------------------------------------------"
	sTemp = sTemp & chr(10)	
	while oResult.next
'		sTemp = sTemp & oResult.row  & sSpc
		sTemp = sTemp & oResult.getstring(1) & sSpc
		sTemp = sTemp & oResult.getstring(2) & sSpc
		sTemp = sTemp & oResult.getstring(3) & sSpc
		sTemp = sTemp & oResult.getstring(4) & sSpc
		sTemp = sTemp & oResult.getstring(5) & sSpc
		sTemp = sTemp & chr(10)	
	wend

	msgbox(sTemp)
	oCon.close
End Sub

[lonk]

Thank you so much for your kind assistance and sorry for my question was unclear.
The point is your sql statement could not be used in Create Query in SQL View.... in Base, but it works great in direct SQL Tools > SQL menu. So, I thought I could not use in macro. On the other hand, it also worked great in macro.

Option Explicit

Function ConnectDatabase(dbFilename As String) As Object
	Dim dbContext 	As Object : dbContext = createUNOService("com.sun.star.sdb.DatabaseContext")
	Dim oDataSource As Object : oDataSource = dbContext.GetByName(dbFilename)
	ConnectDatabase = oDataSource.GetConnection("","")'("Username","Password")
End Function

Sub DisconnectDatabase(db)
	db.Close
	db.Dispose()
End Sub

Sub ViewAddressBook

	Dim oDoc As Object : oDoc = ThisComponent
	Dim oSheet As Object : oSheet = oDoc.CurrentController.ActiveSheet
	Dim i&, r&

    On Local Error GoTo CloseConn
	Dim db 			AS Object 	: db 			= ConnectDatabase("file:///home/id00003/Documents/LibreOffice/Firebird0041.odb")
	Dim oStatement 	As Object 	: oStatement	= db.CreateStatement
	Dim oSQL$
	oSQL = 	"SELECT " & _
	"""CustomerCode"", " & _
	"""CustomerName"", " & _
	"'' ""BranchCode"", " & _
	"""CustomerAddress1"" ""Address1"", " & _
	"""CustomerAddress2"" ""Address2"" " & _
	"FROM ""Customer"" " & _
	"UNION ALL " & _
	"SELECT " & _
	"""CompanyCode"", " & _
	"'Company ' || ""CompanyCode"", " & _
	"""BranchCode"", " & _
	"""BranchAddress1"", " & _
	"""BranchAddress2"" " & _
	"FROM " & _
	"""Branch"" " & _
	"ORDER BY " & _
	"1, 3"

	Dim oResult 	As Object	: oResult 		= oStatement.ExecuteQuery(oSQL)
	
	If Not IsNull(oResult) Then
		While oResult.next'()		oResult.Next
			For i = 1 To 5
				oSheet.getCellByPosition(i - 1, r).String = oResult.getString(i)
			Next
			r = r + 1		
		Wend
	End If
	DisconnectDatabase(db)
	MsgBox "Finished",,"Result"	
	Exit Sub

CloseConn:
    MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")"
    DisconnectDatabase(db)
	
End Sub

I also have to learn more on this syntax

from your question.

[Ratslinger]

Hello,
Your comment:

@lonk

I also have to learn more on this syntax

is relatively simple. It creates an empty field named BranchCode. Then the question is Why do this?
With Union or Union All you are creating a result set generated from multiple select statements. Each of these result sets must have the same number of fields selected. Since there is no branch code in your Customer table and one is in the Company table and needed for sorting, an empty field is created in the select statement for the Customer result then giving both selects the same number of fields.
.
Also, I would avoid using ordinal numbers for sorting. While it works, if your select items change you also must remember to modify this.

[Villeroy]

Every SQL tutorial of the past 30 years explains operators UNION, INTERSECT and EXCEPT
A: 1,2,3,4,5
B: 3,4,5,6,7
SELECT column FROM A UNION SELECT column FROM B ==> 1,2,3,4,5,6,7 (no duplicates)
SELECT column FROM A UNION ALL SELECT column FROM B ==> 1,2,3,3,4,4,5,5,6,7 (keep duplicates)
SELECT column FROM A EXCEPT SELECT column FROM B ==> 1,2 (each A not in B)
SELECT column FROM A INTERSECT SELECT column FROM B ==> 3,4,5

[cpb]

@lonk

The point is your sql statement could not be used in Create Query in SQL View.... in Base, but it works great in direct SQL Tools > SQL menu

maybe you misunderstand the meaning of direct mode?
.
hit Create Query in SQL View…
a window opens into which we can type, paste, edit and execute SELECT statements.
activate the icon Run SQL command directly.
you are now in direct mode.
.
when the SQL is executed:
the Base parser is bypassed which ensures that the SQL is passed to the database unaltered.
SELECT statements will be executed directly by the database.
when you save the query then text formatting will be preserved.
.
the Base parser has many faults/restrictions and often rejects perfectly legitimate SQL.
the majority of developers will always use direct mode whenever possible.
.
you must use the Base parser when passing parameters i.e. main-forms with sub-forms, sub-forms, parameter queries etc.
.
menu:Tools>SQL:
may be used to execute SELECT statements in direct mode.
MUST BE USED FOR:
Data Definition Language (DDL) statements i.e. CREATE, ALTER, DROP etc.
Data Manipulation Language (DML) statements i.e. INSERT, UPDATE, DELETE etc.

[Ratslinger]

It is possible in the Query section you can have statements like Insert and Delete. This depends upon the database and connector type used and that Run SQL command directly is on. You will get an error message but the command will complete.