Datastore sql statement questions

[brady4747]

I am working on modifying the default syncserv_odbc.xml that came with the pro server trial to work with preexisting mysql db. I am wondering how to tailor the sql statements(selectdatasql etc> for my db which is somewhat normalized. The company info is all in separate table from the contacts table which is good db design I guess since more often than not you have more than one person for each company. Of course I know how to use joins and everything to get all the data but did not know how that would work with %N and other variables and the fieldmap. I guess I just need some guidance on using joins for relational data in the sql statements and fieldmap. OR since I am using mysql 5 should I just create a view, although I don't know if that would be updateable though. here is the hypothetical simplified table structure of contacts and company:

CREATE TABLE `contacts` (
`idcontact` int(10) NOT NULL auto_increment,
`idcompany` int(10) default NULL,
`contact_first_name` varchar(20) default NULL,
`contact_last_name` varchar(20) default NULL,
`contact_email` varchar(45) default NULL,
`contact_fax` varchar(10) default NULL,
`contact_business_phone` varchar(17) default NULL,
`contact_home_phone` varchar(10) default NULL,
`contact_mobile_phone` varchar(10) default NULL,
`contact_pager_phone` varchar(10) default NULL,
`contact_website` varchar(45) default NULL,
`contact_birthdate` date default NULL,
`contact_sex` char(1) default NULL,
`contact_street_number` varchar(7) default NULL,
`contact_street_name` varchar(45) default NULL,
`contact_city` varchar(30) default NULL,
`contact_state` char(2) default NULL,
`contact_zip` varchar(5) default NULL,
PRIMARY KEY (`idcontact`)
) ENGINE=MyISAM


CREATE TABLE `company` (
`idcompany` int(10) NOT NULL auto_increment,
`company_name` varchar(45) default NULL,
`modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`created` timestamp NOT NULL default '0000-00-00 00:00:00',
`company_street_number` varchar(6) default NULL,
`company_street_name` varchar(30) default NULL,
`company_city` varchar(30) default NULL,
`company_state` char(2) default NULL,
`company_zip` varchar(5) default NULL,
`company_phone` varchar(10) default NULL,
`company_fax` varchar(10) default NULL,
`company_website` varchar(255) default NULL,
`company_login` varchar(20) default NULL,
`company_password` varchar(20) default NULL,
`company_path` varchar(45) default NULL,
PRIMARY KEY (`idcompany`)
) ENGINE=MyISAM

As you can tell, only the idcompany is stored for each contact, not the entire company name.

Thanks

[brady4747]

OK, I succesfully put the JOIN in the selectdatasql:

SELECT %N FROM contacts LEFT JOIN company on contacts.idcompany = company.idcompany WHERE idcontact=%k AND folderkey=%f

and put the company.company_name in the fieldmap for ORG_NAME and it syncs to device fine. I guess I need to set the ORG_NAME to r instead of rw though in case of changes on client

[brady4747]

I have been reading the docs on this. Would the array tag (9.19.17 SySync Config Reference) better handle the db relationship between contacts and company like above?

[brady4747]

Talking to myself again, I promise I am not crazy Just posting in case it helps someone else and in case I forget.

I also was able to get the company name from the related table by using:

<map name="company_name" references="ORG_NAME" type="string" mode="r"
keyfield="idcompany"
readblobsql="SELECT company_name FROM company WHERE idcompany=%K"/>