Board index » delphi » An SQL query joining two tables

An SQL query joining two tables

I have three Tables in the following relation:
MainTable             :  MainID   -  OTHER FIELDS

Crosstable           :  SrcKey    -  MainID

Keyords Table      :  SrcKEY  -  KeyWOrd

Each MainId in the Main Table may occurr one or more times in the
Crosstable.
To explain better, say the MainTable represents books titles; each book may
be identified by one or several keywords (stored in the Crosstable and named
in the Keywords Table). See the following example:

MainTable
0001 Delphi 5           ............  ...........
0002 Mathematics     ............  ...........

Keywordstable
1    Reference
2    Antique
3    Thriller
4    Info technology
5    School

Crosstable
0001    1
0001    4
0002    1
0001    5

I am trying to show this relationship in a DBGrid. Something like the
following:
   title                              keywords
Delphi 5                    Info technology,  Reference,
Mathematics              Reference, School, .........

I tried using different Sql Queries but I am always ending up with th same
title duplicated as many time as many keywords it has.
I am using the BDE, since the underlying table is a dBase file, so I am
limited to local SQL. I only hope I am the limiting factor. Can anyone help
?

 

Re:An SQL query joining two tables


There's nothing in LocalSQL to do what you want. Some SQL dialects have
functions that do what you want (e.g. LIST() in SQL Anywhere) but generally
you have to do it with client-side code in the application, or with a stored
procedure in databases that permit one (BDE does not).

  Richard

Quote
"Franco Jommi" <franco.jom...@tin.it> wrote in message

news:mzvC9.29071$744.1048171@news1.tin.it...
Quote
> I am trying to show this relationship in a DBGrid. Something like the
> following:
>    title                              keywords
> Delphi 5                    Info technology,  Reference,
> Mathematics              Reference, School, .........

> I tried using different Sql Queries but I am always ending up with th same
> title duplicated as many time as many keywords it has.
> I am using the BDE, since the underlying table is a dBase file, so I am
> limited to local SQL. I only hope I am the limiting factor. Can anyone

help

Re:An SQL query joining two tables


You could add a calculated field to the Maintable which 'concatenates' the
keywords.
The crosstable could be in a master-detail relation with the Maintable.
Or use a query (say QRYKeywords) and let it be mastered (via
query.datasource) by the Maintable:
'select * from crosstable join keyword on keyword.scrkey=crosstable.scrkey'

Then to calc the calculated field:
QRYKeywords.First;
while not QRYKeywords.EOF do
    begin
    field := field+ QRYKeywords.Values[Keyword];
 QRYKeywords.Next
 end;

Quote
Franco Jommi wrote in message ...
>I have three Tables in the following relation:
>MainTable             :  MainID   -  OTHER FIELDS

>Crosstable           :  SrcKey    -  MainID

>Keyords Table      :  SrcKEY  -  KeyWOrd

>Each MainId in the Main Table may occurr one or more times in the
>Crosstable.
>To explain better, say the MainTable represents books titles; each book may
>be identified by one or several keywords (stored in the Crosstable and
named
>in the Keywords Table). See the following example:

>MainTable
>0001 Delphi 5           ............  ...........
>0002 Mathematics     ............  ...........

>Keywordstable
>1    Reference
>2    Antique
>3    Thriller
>4    Info technology
>5    School

>Crosstable
>0001    1
>0001    4
>0002    1
>0001    5

>I am trying to show this relationship in a DBGrid. Something like the
>following:
>   title                              keywords
>Delphi 5                    Info technology,  Reference,
>Mathematics              Reference, School, .........

>I tried using different Sql Queries but I am always ending up with th same
>title duplicated as many time as many keywords it has.
>I am using the BDE, since the underlying table is a dBase file, so I am
>limited to local SQL. I only hope I am the limiting factor. Can anyone help
>?

Other Threads