U++ forum: Welcome to the forum

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

Report message to a moderator