There must be a way..

Thread View: comp.lang.pascal.delphi.databases

3 messages

[T] Back to Threads [V] Tree View [G] Group Articles

3 total messages Started by Ian Jennings Tue, 15 Oct 2002 15:13

#202

Author: Ian Jennings
Date: Tue, 15 Oct 2002 15:13

67 lines
3059 bytes

Hi all,

I have two questions. I don't doubt that both have very simple answers
but I'm blowed if I can see them. I reckon I've been staring at this
damn screen too long. 

Question 1:

I have three tables:

TableA:

AKey Integer
AName String

with values ((1, 'Name1'), (2, 'Name2'))

TableB:
BKey Integer
BDate Date

with values ((101, '1/2/2001'), (102, '2/3/2001'), (103, '9/8/2000))

TableC:
AKey Integer
BKey Integer
CValue Float

with values ((1, 101, 22.3), (2, 101, 44.1), (1, 102, 11.4), (1, 103,
18.5), (2, 103, 17.9))

What I'm trying to get is: (so long as I manage to get this typed in
right <g>)

'1/2/2001', 'Name1', 22.3
'1/2/2001', 'Name2', 44.1
'2/3/2001', 'Name1', 11.4
'2/3/2001', 'Name2', null or 0 or anything
'9/8/2000', 'Name1', 18.5
'9/8/2000', 'Name2', 17.9

I *thought* that an outer join would do this (it says that it will put
nulls in if there's no match) but I can't get this combination.

Question 2:

Using the above format I'm *trying* to, eventually, get a master/detail
onto a single form with three, or more, tables involved and where TableB
is master with TableC as detail in a TDBGrid. No big deal and I've done
that. My problem is that when I fire off an INSERT from a navigator
control I don't get to keep the 'Name1' and 'Name2' values in the grid.
It all gets cleared. Now ISTR having this problem before and I *think* I
worked around it by posting off INSERTS to the equivalents of TableB and
TableC and then modifying them later. It feels a bit messy and I'm sure
there's got to be an easier way.

As ever, all help/advice/pointers greatfully received.

TIA

-- 
************************************************************
Ian Jennings
Microware Data Services Limited

This post is made entirely from recycled ones and noughts
************************************************************

Re: There must be a way..

#200

Author: Ed Edwards, Jr.
Date: Wed, 16 Oct 2002 00:11

17 lines
1517 bytes

This did seem simple at first, but I can't find an SQL statement that will
do it all in one shot.

The best I could come up with was:

Create a temp table containing all the fields from TableA and TableB and
then - insert into TableTemp select * from TableA, TableB

Then -
select AName, BDate, CValue
from TableTemp left join TableC
on (TableA.Akey = TableC.Akey) and
     (TableB.Bkey = TableC.Bkey)

I WOULD have expected a better way!

Ed

Re: There must be a way..

#201

Author: Ian Jennings
Date: Wed, 16 Oct 2002 05:35

39 lines
2416 bytes

"Ed Edwards, Jr." wrote:
> 
> This did seem simple at first, but I can't find an SQL statement that will
> do it all in one shot.
> 
> The best I could come up with was:
> 
> Create a temp table containing all the fields from TableA and TableB and
> then - insert into TableTemp select * from TableA, TableB
> 
> Then -
> select AName, BDate, CValue
> from TableTemp left join TableC
> on (TableA.Akey = TableC.Akey) and
>      (TableB.Bkey = TableC.Bkey)
> 
> I WOULD have expected a better way!
> 
> Ed

Hi Ed,

I've *sort* of done it with a work-around. I've created a proc on the
server that does the inserts into TableB and TableC and returns the key,
then I catch the 'insert' in the app, cancel, put the key into the
master table (TableB) TTable and set both tables to Edit.

It's not quite what I had in mind and I'm sure it's going to lead to
other problems later, a work-around usually does. :) 

Thanks for your help.

-- 
************************************************************
Ian Jennings
Microware Data Services Limited

This post is made entirely from recycled ones and noughts
************************************************************

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads