Cant't do it in Local SQL! 
Author Message
 Cant't do it in Local SQL!

Hi everybody!

I wonder if there is a solution to the following *EVERY DAY PROBLEM* within
Local SQL:

Suppose you have a table FAMILIES  of family names, and a second table NAMES
containing first names of particular family members with a 1:n realationship
imposed to the FAMILIES table. Then is it possible, using a single SQL
statement, to select all the families that have no member with first name
e.g "Andy"?

If you naivly try something like

Select DISTINCT Family from FAMILIES, NAMES where
  (FAMILIES.COUNTER=NAMES.SUBFAMILIES)
  and (Names.Name<>"Andy")

the resulting dataset of course also contains any record referring to a family
where there is at least any OTHER member not called "Andy" (it is basically
some subset of the Cartesian product of both tables!).

I tried all kinds of left and right join constructions, but did not succeed.
Of course, in e.g. Interbase the solution is easy (use a subquery!) but
I doubt it can be done in LOCAL SQL. Even worse, I was unable to solve the
problem without resorting to building an auxiliary table with a TBatchmove
that contais all "unwanted" records.

Will BDE32 support subqueries in Local SQL?

Any comments appreciated,

                                                  Andy



Fri, 24 Jul 1998 03:00:00 GMT  
 Cant't do it in Local SQL!


: >>
: >> Suppose you have a table FAMILIES  of family names, and a second table NAMES
: >> containing first names of particular family members with a 1:n realationship
: >> imposed to the FAMILIES table. Then is it possible, using a single SQL
: >> statement, to select all the families that have no member with first name
: >> e.g "Andy"?
: >>
: >> If you naivly try something like
: >>
: >> Select DISTINCT Family from FAMILIES, NAMES where
: >>   (FAMILIES.COUNTER=NAMES.SUBFAMILIES)
: >>   and (Names.Name<>"Andy")
: >>
: >> the resulting dataset of course also contains any record referring to a family
: >> where there is at least any OTHER member not called "Andy" (it is basically
: >> some subset of the Cartesian product of both tables!).
: >>
: >  I have exactly the same problem.  Please help us out!

: I seem to have missed the first question, but from the subject, I
: guess that's against Paradox because you cannot execute nested queries
: in Local SQL (while they work quite well on Local Interbase and other
: true SQL servers). However, just in case, here's one working on Local
: Interbase:

:       select distinct naam from families,subfamilies
:       where families.id=subfamilies.id
:       and 'Jasper' not in
:       ( select voornaam from subfamilies sf
:         where families.id=sf.id )

: I don't say it's the most efficient one (there are others), but it's a
: first go.

: As for Paradox, you could do that "manually" with Database Desktop.
: The idea is to make an answer table with a first query selecting all
: families with the UNDESIRED member, then subtracting that table from
: the whole familie-table (from the menu: Utilities|Subtract).

: Emulating that in Delphi, I build a form with only a few components on
: it:
:       - 2 TQueries
:       - 1 TBatchMove
:       - 1 TTable
:       - 1 TDatasource
:       - 1 TDBGrid
:       - 1 TButton
: The relevant properties were set to the following (it's an excerpt
: from my .DFM, so don't mind the syntax):

: object Form1: TForm1
:   ...
:   object DBGrid1: TDBGrid
:     ...
:     DataSource = DataSource1
:   end
:   object btnExecuteSelection: TButton
:     ...
:     OnClick = btnExecuteSelectionClick
:   end
:   object qrySelectFromResult: TQuery
:     DatabaseName = 'SRKParadox'
:     SQL.Strings = (
:       'select naam from result'
:       'where voornamen is null')
:     ...
:   end
:   object BatchMove1: TBatchMove
:     ChangedTableName = 'changed'
:     Destination = tblResult
:     KeyViolTableName = 'keyviol'
:     Mode = batCopy
:     ProblemTableName = 'problems'
:     Source = qrySelectAll
:     ...
:   end
:   object tblResult: TTable
:     DatabaseName = 'SRKParadox'
:     TableName = 'result'
:     ...
:   end
:   object qrySelectAll: TQuery
:     Active = True
:     DatabaseName = 'SRKParadox'
:     SQL.Strings = (
:       'select distinct families.naam, subfam.voornamen'
:       'from families left outer join subfam on families.id=subfam.id'
:       'where subfam.voornamen = 'Jasper')
:     ...
:   end
:   object DataSource1: TDataSource
:     DataSet = qrySelectFromResult
:     ...
:   end
: end

: In the OnClick event-handler for that button, I added just two lines:
:       BatchMove1.Execute;
:       qrySelectFromResult.Open;

: Don't forget to change the names in the components to what you want
: them to be. I used two tables:
:       - Families
:               ID              SHORT
:               NAAM            ALFA 20
:       - Subfam
:               ID              SHORT
:               VOORNAMEN       ALFA 20

: Here too, I don't say it's the most efficient way, though I believe
: that it is the easiest. Another way around would be simply looping
: through the query-result, but that would impose some administration as
: to when family-name changes while some boolean "AndyFound" remains
: false etc... (in short, I don't like that).

: I'm sorry for this monster of text, but I couldn't possibly shorten it
: further.

: Hope it helps you out. Feel free to ask if there's something you don't
: understand.

: Good luck!

: Jasper

: PS: please take into consideration
: - when replying, I just think I know;
: - when asking, be sure that I don't.

Thanks a lot for responding! Your code is definitely a solution to this.
Still it is open to me if there will be nested queries in Local SQL under
Delphi32. All I know up to now is that Local SQL will conform to some
"ANSI92 standard". Does that mean it does? It would be a big advantage,
since

-- The "auxiliary" table that contains the unwanted records can be almost as
large as the whole detail table (which in my situation is huge)

-- In a network situation, each user would have to have his privat local
auxiliary table

Anyway, I appreciate your answer very much, since it really solves the
problem. Thanx,

                                             Andy



Sun, 26 Jul 1998 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. GROUP BY in Delphi's Local SQL

2. Delphi 3 cant (won't?) DELETE records

3. Using LOCAL-SQL or Transact-SQL with SQL-server and BDE

4. Cant use hyphen in SQL!!!

5. put 'Local share = Ture' in code

6. What are Y'all doing with Delphi ?

7. BDE doing things it shouldn't

8. TStringList - I'm doing something wrong here - HELP

9. Doing other people's homework

10. SQL: UNION with local SQL?

11. Local SQL v.s MS SQL Server

12. Can BDE MSACCESS use SQL (not local SQL)

 

 
Powered by phpBB® Forum Software