Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » [SOLVED] How to join two tables from two schemas with SqlExp
[SOLVED] How to join two tables from two schemas with SqlExp [message #50841] Tue, 08 January 2019 15:06 Go to next message
Patisab is currently offline  Patisab
Messages: 18
Registered: December 2015
Location: France
Promising Member
Hello,

I have two schemas (S1 and S2) in one database (D1). I have to join one table from S1 with one table from S2. I am connected to the database whith user who is granted as owner of S1 and as reader of S2. Something like that :
SELECT T1.Col1, T1.Col2, S2.T1.Col2, S2.T1.Col3
FROM T1
INNER JOIN S2.T1 ON S2.T1.Col1 = S1.T1.Col1


When i try to do that with SqlExp like this :
Select(Col1.Of(T1),Col2.Of(T1),Col2.Of(T2),Col3.Of(T2))
.From(T1)
.InnerJoin(S2.T1).As(T2).On(Col1.Of(T2) == Col1.Of(T1))

...i obtain an error message that explains S2 is not declared.

So is it possible? If it is, how?

Thank you very much for your help.

Bests regards... and happy new year Very Happy

[Updated on: Wed, 09 January 2019 13:24]

Report message to a moderator

Re: How to join two tables from two schemas with SqlExp [message #50849 is a reply to message #50841] Wed, 09 January 2019 09:22 Go to previous messageGo to next message
mirek is currently online  mirek
Messages: 11825
Registered: November 2005
Ultimate Member
Patisab wrote on Tue, 08 January 2019 15:06
So is it possible? If it is, how?


#include <Core/Core.h>
#include <plugin/sqlite3/Sqlite3.h>

using namespace Upp;

CONSOLE_APP_MAIN
{
	Sqlite3Session sqlite3;
	
	sqlite3.SetTrace();
	
	SQL = sqlite3;

	SqlId T1("T1"), Col1("Col1"), Col2("Col2");
	SQLID(T2); SQLID(S2);

	SQL * Select(T1(Col1), T1(Col2), T2(Col2), T2(Col2))
	      .From(T1)
	      .InnerJoin(S2(T1).As(T2))
	      .On(T2(Col1) == T1(Col2));
}


produces

select T1.Col1, T1.Col2, T2.Col2, T2.Col2 from T1 inner join S2.T1 T2 on T2.Col1 = T1.Col2


SQLID(T1) and SqlId T1("T1") are equivalent. Also, if you are using .sch files, you can do the same thing by adding '_' to type (like INT_ (Col1) or TABLE_(T1)).

Col1.Of(T1) and T1(Col1) are equivalent (parenthesis form is 'more modern').

You cannot write "S2.T1" - we cannot support that, as C++ does not have "operator." overloading Smile

Mirek

[Updated on: Wed, 09 January 2019 09:23]

Report message to a moderator

Re: How to join two tables from two schemas with SqlExp [message #50850 is a reply to message #50849] Wed, 09 January 2019 09:55 Go to previous messageGo to next message
Patisab is currently offline  Patisab
Messages: 18
Registered: December 2015
Location: France
Promising Member
Good morning Mirek,

Well, the solution is looking like this for two aliases of the same table that i asked for a few months ago. I will try it soon.

Thank you very much.

Best regards.
Re: How to join two tables from two schemas with SqlExp [message #50853 is a reply to message #50850] Wed, 09 January 2019 13:23 Go to previous message
Patisab is currently offline  Patisab
Messages: 18
Registered: December 2015
Location: France
Promising Member
I can compile and le SQL request works very well. Thank you.

Mirek, you have got the same nickname as John Eales : Nobody, because Nobody is perfect Laughing

Best regards.
Previous Topic: OleDBSession in Archive
Next Topic: Connect to TWO databases
Goto Forum:
  


Current Time: Fri Apr 19 12:45:17 CEST 2019

Total time taken to generate the page: 0.00691 seconds