Board index » delphi » problem to join sql queries

problem to join sql queries

I created with Access 2 queries, the second is linked to the first. I
cut and past the sql code produced by Access into my delphi source :

When I try to run, I get the message :
[...]The Microsoft Jet database engine cannot find the input table or
query 'quer1' [...]

here is my code, Can anyone tell me what I missed ? thanks a lot.

var
   data : tdatabase;
   query1,query2 : tquery;

begin
data:=tdatabase.create(nil);
data.aliasname:='internships';
data.databasename:='mydb';
data.loginprompt:=false;
data.connected:=true;

query1:=tquery.create(nil);
query1.databasename:='mydb';

query1.close;

   Query1.SQL.clear;
   Query1.SQL.add(

   'SELECT internships.paid, internships.locationID, internships.siteID,

internships.internship, internships.qualification,
internships.description, '
   +'internships.application, Sites.Site, Sites.web, locations.location,

Contacts.firstname, Contacts.initial, Contacts.lastname, Contacts.Title,

'
   +'Contacts.Phone, Contacts.Ext, Contacts.Email, Contacts.Fax,
Contacts.Street1, Contacts.Street2, Contacts.City, Contacts.State,
Contacts.Zip, '
   +'Contacts.Alumni FROM Sites INNER JOIN (Contacts INNER JOIN
(locations INNER JOIN internships ON locations.locationID =
internships.locationID) '
   +'ON Contacts.ContactID = internships.contactID) ON Sites.SiteID =
internships.siteID WHERE (((internships.paid)=True Or
(internships.paid)=False));'

   );

   query1.prepare;
   Query1.open;

query2:=tquery.create(nil);
query2.databasename:='mydb';

query2.close;

   query2.SQL.clear;
   query2.sql.add(

   'SELECT types.type, interntypes.typeID, query1.*, query1.paid,
query1.siteID, query1.internship, query1.qualification, '
   +'query1.description, query1.application, query1.Site, query1.web,
query1.location, query1.firstname, query1.initial, '
   +'query1.lastname, query1.Title, query1.Phone, query1.Ext,
query1.Email, query1.Fax, query1.Street1, query1.Street2, '
   +'query1.City, query1.State, query1.Zip, query1.Alumni FROM query1
INNER JOIN (types INNER JOIN interntypes ON '
   +'types.typeid = interntypes.typeID) ON query1.siteID =
interntypes.siteID WHERE (((interntypes.typeID)=1));'

);

query2.prepare;
query2.open;

 

Re:problem to join sql queries


You can't do that in Delphi, it doesn't accept a query like source of
other query. You need to merge the two querys in one.
Quote
Philippe Benthien wrote:

> I created with Access 2 queries, the second is linked to the first. I
> cut and past the sql code produced by Access into my delphi source :

> When I try to run, I get the message :
> [...]The Microsoft Jet database engine cannot find the input table or
> query 'quer1' [...]

> here is my code, Can anyone tell me what I missed ? thanks a lot.

> var
>    data : tdatabase;
>    query1,query2 : tquery;

> begin
> data:=tdatabase.create(nil);
> data.aliasname:='internships';
> data.databasename:='mydb';
> data.loginprompt:=false;
> data.connected:=true;

> query1:=tquery.create(nil);
> query1.databasename:='mydb';

> query1.close;

>    Query1.SQL.clear;
>    Query1.SQL.add(

>    'SELECT internships.paid, internships.locationID, internships.siteID,

> internships.internship, internships.qualification,
> internships.description, '
>    +'internships.application, Sites.Site, Sites.web, locations.location,

> Contacts.firstname, Contacts.initial, Contacts.lastname, Contacts.Title,

> '
>    +'Contacts.Phone, Contacts.Ext, Contacts.Email, Contacts.Fax,
> Contacts.Street1, Contacts.Street2, Contacts.City, Contacts.State,
> Contacts.Zip, '
>    +'Contacts.Alumni FROM Sites INNER JOIN (Contacts INNER JOIN
> (locations INNER JOIN internships ON locations.locationID =
> internships.locationID) '
>    +'ON Contacts.ContactID = internships.contactID) ON Sites.SiteID =
> internships.siteID WHERE (((internships.paid)=True Or
> (internships.paid)=False));'

>    );

>    query1.prepare;
>    Query1.open;

> query2:=tquery.create(nil);
> query2.databasename:='mydb';

> query2.close;

>    query2.SQL.clear;
>    query2.sql.add(

>    'SELECT types.type, interntypes.typeID, query1.*, query1.paid,
> query1.siteID, query1.internship, query1.qualification, '
>    +'query1.description, query1.application, query1.Site, query1.web,
> query1.location, query1.firstname, query1.initial, '
>    +'query1.lastname, query1.Title, query1.Phone, query1.Ext,
> query1.Email, query1.Fax, query1.Street1, query1.Street2, '
>    +'query1.City, query1.State, query1.Zip, query1.Alumni FROM query1
> INNER JOIN (types INNER JOIN interntypes ON '
>    +'types.typeid = interntypes.typeID) ON query1.siteID =
> interntypes.siteID WHERE (((interntypes.typeID)=1));'

> );

> query2.prepare;
> query2.open;

Re:problem to join sql queries


Quote
Jos Gerez Morata wrote:
> You can't do that in Delphi, it doesn't accept a query like source of
> other query. You need to merge the two querys in one.

a *almost* found a solution : my first query creates a view
and the second query refers to this view instead of the first query.

It works well only the first time, because once the view is created, it stay
recorded in the Access file, and an attempt to recreate this view the second
time results in a SQL error.

so, how do you delete a view  ?.

Thanks for your support.
Ph.B.

Quote

> Philippe Benthien wrote:

> > I created with Access 2 queries, the second is linked to the first. I
> > cut and past the sql code produced by Access into my delphi source :

> > When I try to run, I get the message :
> > [...]The Microsoft Jet database engine cannot find the input table or
> > query 'quer1' [...]

> > here is my code, Can anyone tell me what I missed ? thanks a lot.

> > var
> >    data : tdatabase;
> >    query1,query2 : tquery;

> > begin
> > data:=tdatabase.create(nil);
> > data.aliasname:='internships';
> > data.databasename:='mydb';
> > data.loginprompt:=false;
> > data.connected:=true;

> > query1:=tquery.create(nil);
> > query1.databasename:='mydb';

> > query1.close;

> >    Query1.SQL.clear;
> >    Query1.SQL.add(

> >    'SELECT internships.paid, internships.locationID, internships.siteID,

> > internships.internship, internships.qualification,
> > internships.description, '
> >    +'internships.application, Sites.Site, Sites.web, locations.location,

> > Contacts.firstname, Contacts.initial, Contacts.lastname, Contacts.Title,

> > '
> >    +'Contacts.Phone, Contacts.Ext, Contacts.Email, Contacts.Fax,
> > Contacts.Street1, Contacts.Street2, Contacts.City, Contacts.State,
> > Contacts.Zip, '
> >    +'Contacts.Alumni FROM Sites INNER JOIN (Contacts INNER JOIN
> > (locations INNER JOIN internships ON locations.locationID =
> > internships.locationID) '
> >    +'ON Contacts.ContactID = internships.contactID) ON Sites.SiteID =
> > internships.siteID WHERE (((internships.paid)=True Or
> > (internships.paid)=False));'

> >    );

> >    query1.prepare;
> >    Query1.open;

> > query2:=tquery.create(nil);
> > query2.databasename:='mydb';

> > query2.close;

> >    query2.SQL.clear;
> >    query2.sql.add(

> >    'SELECT types.type, interntypes.typeID, query1.*, query1.paid,
> > query1.siteID, query1.internship, query1.qualification, '
> >    +'query1.description, query1.application, query1.Site, query1.web,
> > query1.location, query1.firstname, query1.initial, '
> >    +'query1.lastname, query1.Title, query1.Phone, query1.Ext,
> > query1.Email, query1.Fax, query1.Street1, query1.Street2, '
> >    +'query1.City, query1.State, query1.Zip, query1.Alumni FROM query1
> > INNER JOIN (types INNER JOIN interntypes ON '
> >    +'types.typeid = interntypes.typeID) ON query1.siteID =
> > interntypes.siteID WHERE (((interntypes.typeID)=1));'

> > );

> > query2.prepare;
> > query2.open;

Re:problem to join sql queries


Use DROP TABLE ViewName
Quote
Philippe Benthien wrote:

> Jos Gerez Morata wrote:

> > You can't do that in Delphi, it doesn't accept a query like source of
> > other query. You need to merge the two querys in one.

> a *almost* found a solution : my first query creates a view
> and the second query refers to this view instead of the first query.

> It works well only the first time, because once the view is created, it stay
> recorded in the Access file, and an attempt to recreate this view the second
> time results in a SQL error.

> so, how do you delete a view  ?.

> Thanks for your support.
> Ph.B.

> > Philippe Benthien wrote:

> > > I created with Access 2 queries, the second is linked to the first. I
> > > cut and past the sql code produced by Access into my delphi source :

> > > When I try to run, I get the message :
> > > [...]The Microsoft Jet database engine cannot find the input table or
> > > query 'quer1' [...]

> > > here is my code, Can anyone tell me what I missed ? thanks a lot.

> > > var
> > >    data : tdatabase;
> > >    query1,query2 : tquery;

> > > begin
> > > data:=tdatabase.create(nil);
> > > data.aliasname:='internships';
> > > data.databasename:='mydb';
> > > data.loginprompt:=false;
> > > data.connected:=true;

> > > query1:=tquery.create(nil);
> > > query1.databasename:='mydb';

> > > query1.close;

> > >    Query1.SQL.clear;
> > >    Query1.SQL.add(

> > >    'SELECT internships.paid, internships.locationID, internships.siteID,

> > > internships.internship, internships.qualification,
> > > internships.description, '
> > >    +'internships.application, Sites.Site, Sites.web, locations.location,

> > > Contacts.firstname, Contacts.initial, Contacts.lastname, Contacts.Title,

> > > '
> > >    +'Contacts.Phone, Contacts.Ext, Contacts.Email, Contacts.Fax,
> > > Contacts.Street1, Contacts.Street2, Contacts.City, Contacts.State,
> > > Contacts.Zip, '
> > >    +'Contacts.Alumni FROM Sites INNER JOIN (Contacts INNER JOIN
> > > (locations INNER JOIN internships ON locations.locationID =
> > > internships.locationID) '
> > >    +'ON Contacts.ContactID = internships.contactID) ON Sites.SiteID =
> > > internships.siteID WHERE (((internships.paid)=True Or
> > > (internships.paid)=False));'

> > >    );

> > >    query1.prepare;
> > >    Query1.open;

> > > query2:=tquery.create(nil);
> > > query2.databasename:='mydb';

> > > query2.close;

> > >    query2.SQL.clear;
> > >    query2.sql.add(

> > >    'SELECT types.type, interntypes.typeID, query1.*, query1.paid,
> > > query1.siteID, query1.internship, query1.qualification, '
> > >    +'query1.description, query1.application, query1.Site, query1.web,
> > > query1.location, query1.firstname, query1.initial, '
> > >    +'query1.lastname, query1.Title, query1.Phone, query1.Ext,
> > > query1.Email, query1.Fax, query1.Street1, query1.Street2, '
> > >    +'query1.City, query1.State, query1.Zip, query1.Alumni FROM query1
> > > INNER JOIN (types INNER JOIN interntypes ON '
> > >    +'types.typeid = interntypes.typeID) ON query1.siteID =
> > > interntypes.siteID WHERE (((interntypes.typeID)=1));'

> > > );

> > > query2.prepare;
> > > query2.open;

Other Threads