Home » U++ Library support » U++ SQL » SQL to SqlExp
SQL to SqlExp [message #22080] |
Tue, 16 June 2009 21:23 |
andrei_natanael
Messages: 262 Registered: January 2009
|
Experienced Member |
|
|
I've tried to join some tables(multiple times) and i've used something like CITY.Of(CCY).As(CURRENT_CITY) but without success. Actually it works but i have to create aliases for tables and i don't know how to do it with SqlExp, so right now i'm in the point of using SQL directly to create my query. I know it's not the U++ way of doing things but still i benefit from SqlExp while fetching results, so it's not that bad.
sql.Execute("SELECT ID, FIRST_NAME, LAST_NAME, SEX, MARITAL_STATUS, "
"(SELECT COUNTY FROM COUNTIES WHERE CO_ID = CURRENT_COUNTY_ID) AS CURRENT_COUNTY,"
"(SELECT CITY FROM CITIES WHERE CI_ID = CURRENT_CITY_ID) AS CURRENT_CITY,"
"CURRENT_ADDRESS, PHONE, "
"(SELECT COUNTY FROM COUNTIES WHERE CO_ID = BIRTH_COUNTY_ID) AS BIRTH_COUNTY,"
"(SELECT CITY FROM CITIES WHERE CI_ID = BIRTH_CITY_ID) AS BIRTH_CITY,"
"BIRTH_ADDRESS, BIRTH_DATE, BAPTISM_DATE, BAPTIZER_NAME, NOTE "
"FROM MEMBERS");
// I have to add a Where clause later to filter the output based on some conditions
TABLE_(COUNTRIES)
INT_ (CY_ID) PRIMARY_KEY
STRING_ (COUNTRY, 200)
END_TABLE
TABLE_(COUNTIES)
INT_ (CO_ID) PRIMARY_KEY
STRING_ (COUNTY, 200)
INT_ (COUNTRY_ID) REFERENCES(COUNTRIES)
END_TABLE
TABLE_(CITIES)
INT_ (CI_ID) PRIMARY_KEY
STRING_ (CITY, 200)
INT_ (COUNTY_ID) REFERENCES(COUNTIES)
END_TABLE
TABLE_(MEMBERS)
INT_ (ID) AUTO_INCREMENT PRIMARY_KEY
STRING_ (FIRST_NAME, 200)
STRING_ (LAST_NAME, 200)
INT_ (SEX)
INT_ (MARITAL_STATUS)
DATE_ (BIRTH_DATE)
INT_ (BIRTH_COUNTY_ID)
INT_ (BIRTH_CITY_ID)
STRING_ (BIRTH_ADDRESS, 1024)
INT_ (CURRENT_COUNTY_ID)
INT_ (CURRENT_CITY_ID)
STRING_ (CURRENT_ADDRESS, 1024)
DATE_ (BAPTISM_DATE)
STRING_ (BAPTIZER_NAME, 400)
STRING_ (PHONE, 32)
STRING_ (NOTE, 1024)
END_TABLE
TABLE_(INFO)
STRING_ (PROPERTY, 256) PRIMARY_KEY
STRING_ (VALUE, 1024)
END_TABLE
The problem is that I have to JOIN COUNTIES and CITIES with MEMBERS (each table 2 times). That's because i have to read current county and birth county, current city and birth city from COUNTIES and CITIES and put the result together with informations from MEMBERS into ArrayCtrl.
What is recommended? How to solve this? Using JOIN, or simple join with aliases or creating a VIEW over them or something else?
Thanks in advance, Andrei
|
|
|
|
Goto Forum:
Current Time: Tue Jun 04 00:53:16 CEST 2024
Total time taken to generate the page: 0.02659 seconds
|