Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 6 of 6 total |
SQL question |
Wed, May 17 2006 4:03 AM | Permanent Link |
"Robert Rowlands" | 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. |
Wed, May 17 2006 5:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, May 17 2006 11:02 AM | Permanent Link |
"Robert Rowlands" | 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 > |
Wed, May 17 2006 11:44 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, May 17 2006 3:21 PM | Permanent Link |
"Robert Rowlands" | 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 |
Thu, May 18 2006 2:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |