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

"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

"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

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

"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. (joke, ok?)

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