Hello,  sorry the subject is not more helpful.

I have two tables, Table a and Table b.

I want a load of fields from Table a plus one from Table b.

Table a contains Time Slips.  Each Time Slip is associated with a Task; the
Tasks are in Table b.  The link is an integer field.

Table b contains not only Tasks, but also Parent Tasks.  So, in an
engineering office a Parent Task might be 'Design'.  The actual (child)
Tasks could be 'Foundations', 'Superstructure', 'Site Investigation' etc.
There is a field ParentID which contains 0 for Parents and the autoinc field
of the Parent for all Tasks.  Hope this makes sense.  This structure is
needed to use DevExpresses Quantum TreeList.

For each Time Slip I need to recover the name of the Task from Table b
(easy) but also the name of the Parent Task.  This is of course another
record in Table b linked to the Task by an integer field.

How can I achieve this?

Thanks.

Rob.

Robert


Something like

SELECT a.f1, a.f2, a.f3, task.f1, parent.f1
FROM
Timesheets a
JOIN Tasks task ON a.ltaskink = task.tasklink
JOIN Tasks parent ON a.parentline = parent.parentlink

essentially you just have to join table b twice


Roy Lambert

Roy

I don't think I have described my need that well.

The field parentlink is on the task record in tasks (i.e. not in table a).

In affect I need to locate the task record in tasks then get the value of
the parentlink field from that record and then use that value to seek the
parent record in the tasks table and then get a field from the parent
record.

Does that make sense?

Thanks.

Rob.





"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:2D33A6AF-1A07-4A16-858A-CBEF3DE2EDB5@news.elevatesoft.com...
> Robert
>
>
> Something like
>
> SELECT a.f1, a.f2, a.f3, task.f1, parent.f1
> FROM
> Timesheets a
> JOIN Tasks task ON a.ltaskink = task.tasklink
> JOIN Tasks parent ON a.parentline = parent.parentlink
>
> essentially you just have to join table b twice
>
>
> Roy Lambert
>

Robert

>I don't think I have described my need that well.
>
>The field parentlink is on the task record in tasks (i.e. not in table a).
>
>In affect I need to locate the task record in tasks then get the value of
>the parentlink field from that record and then use that value to seek the
>parent record in the tasks table and then get a field from the parent
>record.
>
>Does that make sense?

Sure does. That was my initial guess then I read your post again and came up with the alternative interpretation

So something like


select ELN._BoxNo, a._BoxNo, b._BoxNo, _subject, a._BoxName, b._BoxName
FROM ELN
JOIN BandA a ON ELN._BoxNo = a._BoxNo
JOIN BandA b ON a._BelongsTo = b._BoxNo

Just run against a couple of my tables so you'll have to translate

Roy Lambert

Nice one Roy

That's sorted it.

Tell me, I see you on the WPTools forum, do you also use DevExpress?


"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:B0361269-5DCE-4843-8401-2E443CB50C42@news.elevatesoft.com...
> Robert
>
>>I don't think I have described my need that well.
>>
>>The field parentlink is on the task record in tasks (i.e. not in table a).
>>
>>In affect I need to locate the task record in tasks then get the value of
>>the parentlink field from that record and then use that value to seek the
>>parent record in the tasks table and then get a field from the parent
>>record.
>>
>>Does that make sense?
>
> Sure does. That was my initial guess then I read your post again and came
> up with the alternative interpretation
>
> So something like
>
>
> select ELN._BoxNo, a._BoxNo, b._BoxNo, _subject, a._BoxName, b._BoxName
> FROM ELN
> JOIN BandA a ON ELN._BoxNo = a._BoxNo
> JOIN BandA b ON a._BelongsTo = b._BoxNo
>
> Just run against a couple of my tables so you'll have to translate
>
> Roy Lambert

Robert


Nope. As a hobby developer I own very few paid for 3rd party components - DBISAM, WPTools, HTMLEdit, HTMLViewer, ElasticForm.

There are a slew of freeebies and some roll your own.

If I produced software for a living I'd want all the aids I could find / afford, as it is, I "enjoy" producing my own, often with a big push from someone else's code. eg my dbgrid is a modification of Mike Skolnik's.


Roy Lambert