![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
![]() |
Mon, Nov 9 2009 5:21 PM | Permanent Link |
darrell | Hi list:
given a table C with the following structure: ID Integer Name VarChar(30) LinkID Integer LinkIDs (if present) point to an ID in the same table I would like a query to return four fields ID, Name, LinkID, 'LinkName' (where LinkName is empty if LinkID = null or LinkName = Name where LinkID = ID) How can I achieve this? Thanks, Darrell |
Mon, Nov 9 2009 5:35 PM | Permanent Link |
"Robert" | "darrell" <dfunk@hotmail.com> wrote in message news:1DA5A5F2-2EA5-4905-B16A-5F2A7EC7BA85@news.elevatesoft.com... > Hi list: > > given a table C with the following structure: > > ID Integer > Name VarChar(30) > LinkID Integer > > LinkIDs (if present) point to an ID in the same table > > I would like a query to return four fields > > ID, Name, LinkID, 'LinkName' (where LinkName is empty if LinkID = null or > LinkName = Name > where LinkID = ID) > > How can I achieve this? > You link the table to itself select a.id, a.name, a.linkid, b.name from C a left outer join C b on (b.linkid = a.id) You need a left outer join to get every row from table a. Robert |
Mon, Nov 9 2009 6:40 PM | Permanent Link |
darrell | "Robert" wrote:
"darrell" <dfunk@hotmail.com> wrote in message news:1DA5A5F2-2EA5-4905-B16A-5F2A7EC7BA85@news.elevatesoft.com... > Hi list: > > given a table C with the following structure: > > ID Integer > Name VarChar(30) > LinkID Integer > > LinkIDs (if present) point to an ID in the same table > > I would like a query to return four fields > > ID, Name, LinkID, 'LinkName' (where LinkName is empty if LinkID = null or > LinkName = Name > where LinkID = ID) > > How can I achieve this? > You link the table to itself select a.id, a.name, a.linkid, b.name from C a left outer join C b on (b.linkid = a.id) You need a left outer join to get every row from table a. Robert Thanks Robert - but there are some issues with your suggestion: If I use it as written by use, I get fast results which are blatantly wrong (link names where there is no linkid etc.) If I change it to read: .... left outer join C b on (a.linkid = b.id) it returns correct results but is 1000 times slower..... Regards Darrell |
Mon, Nov 9 2009 6:51 PM | Permanent Link |
darrell | darrell wrote:
"Robert" wrote: "darrell" <dfunk@hotmail.com> wrote in message news:1DA5A5F2-2EA5-4905-B16A-5F2A7EC7BA85@news.elevatesoft.com... > Hi list: > > given a table C with the following structure: > > ID Integer > Name VarChar(30) > LinkID Integer > > LinkIDs (if present) point to an ID in the same table > > I would like a query to return four fields > > ID, Name, LinkID, 'LinkName' (where LinkName is empty if LinkID = null or > LinkName = Name > where LinkID = ID) > > How can I achieve this? > You link the table to itself select a.id, a.name, a.linkid, b.name from C a left outer join C b on (b.linkid = a.id) You need a left outer join to get every row from table a. Robert Thanks Robert - but there are some issues with your suggestion: If I use it as written by use, I get fast results which are blatantly wrong (link names where there is no linkid etc.) If I change it to read: .... left outer join C b on (a.linkid = b.id) it returns correct results but is 1000 times slower..... Regards Darrell And the slow speed was due to a missing index on ID I was so sure I had in place! So I am good now once I make the small change to your query mentioned above Thanks, Darrell |
Mon, Nov 9 2009 9:48 PM | Permanent Link |
"Robert" | "darrell" <dfunk@hotmail.com> wrote in message news:68CA4333-A808-4DBF-912D-3B66DBFDEC4D@news.elevatesoft.com... > darrell wrote: > > "Robert" wrote: > > > "darrell" <dfunk@hotmail.com> wrote in message > news:1DA5A5F2-2EA5-4905-B16A-5F2A7EC7BA85@news.elevatesoft.com... >> Hi list: >> >> given a table C with the following structure: >> >> ID Integer >> Name VarChar(30) >> LinkID Integer >> >> LinkIDs (if present) point to an ID in the same table >> >> I would like a query to return four fields > >> >> ID, Name, LinkID, 'LinkName' (where LinkName is empty if LinkID = null or >> LinkName = Name >> where LinkID = ID) >> >> How can I achieve this? >> > > You link the table to itself > > select a.id, a.name, a.linkid, b.name > from C a > left outer join C b on (b.linkid = a.id) > > You need a left outer join to get every row from table a. > > Robert > > > > Thanks Robert - but there are some issues with your suggestion: > > If I use it as written by use, I get fast results which are blatantly > wrong (link names > where there is no linkid etc.) > > If I change it to read: .... left outer join C b on (a.linkid = b.id) > > it returns correct results but is 1000 times slower..... > Well, what do you want, speed or accuracy? Can't have both. ![]() Seriously now, I'm glad you got it working, your code is correct. If you have an index on linkid it will be even faster. Robert |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |