Thanks so much for your response psiEnergos. Here is the code i used
to make this happen..

Ok i've figured this out.. here's how i've done this in infopath 2007.

if ( e.NewValue != "" )
{
DataConnection objADOAdapter;
AdoQueryConnection connection;

objADOAdapter = this.DataSources["NavigationGroups"].QueryConnection;
connection = ( (AdoQueryConnection)( objADOAdapter ) );

// Retrieve the field in the InfoPath form whose value will be used in
the WHERE clause
XPathNavigator node = this.CreateNavigator().SelectSingleNode( "/
my:myFields/my:group10/
my:cbIdentifyAssetFilterTypes",this.NamespaceManager );

if(connection.Command.Contains("inner join"))
{
//i want to remove the inner join text if there is already an inner //
join so that i can append the new join condition.
int index = connection.Command.IndexOf( "inner" );
connection.Command = connection.Command.Remove(index );
}
// Generate the new SQL statement with the INNER JOIN and WHERE clause
string strSQL = connection.Command + " inner join
linker_parent_to_child_nav l on l.childid = NavigationGroups.id where
parentid = '" + node.InnerXml + "'";

connection.Command = strSQL;

this.DataConnections["NavigationGroups"].Execute( );

}