asked on

Join double column in a table, urgent

Hi everybody,

I have two tables,
Rank
RankID
Name
InDelegation
InDelegationName
LeaderRank
InvitedRank
Primarykey of Rank Table is RankID, and Rank table has Name column
The Delegation Table has two FK is LeaderRank and InvitedRank.

So how can I get the following Row

InDelegationID, InDelegationName LeaderRankName, InviteRankName

Please, help me. Thanks for any help.

davee_jay

Below are two ways to do this one using derived tables the other using correlated sub-queries:

--Derived Tables
SELECT d.InDelegationID, d.InDelegationName, l.Name [LeaderRankName], i.Name [InvitedRankName]
FROM InDelegation d
LEFT OUTER JOIN (
      SELECT RankID, Name
      FROM Rank
) l ON l.RankID = d.LeaderRank
LEFT OUTER JOIN (
      SELECT RankID, Name
      FROM Rank
) i ON i.RankID = d.InvitedRank

--Correlated Subqueries (or nested selects)

SELECT d.InDelegationID, d.InDelegationName,
(--Top 1 used to ensure we only get a single row back,
-- otherwise this will throw an error
      SELECT TOP 1 Name
      FROM Rank
      WHERE RankID = d.LeaderRank
) [LeaderRankName],
(--Top 1 used to ensure we only get a single row back,
-- otherwise this will throw an error
      SELECT TOP 1 Name
      FROM Rank
      WHERE RankID = d.InvitedRank
) [InvitedrankName]
FROM InDelegation d

Which you decide to use will depend on performance and size of the Rank table. If in doubt a quick consultation with BOL will reveal the pros and cons of each solution.

Let me know how you get on

DJ

BillAn1

you don't need anything as complicated as nested queries, or derived tables - a straightforward join will do the trick :

select d.InDelagationID, d.InDelegationName, ldr.name AS LeaderRankName, inv.name AS InvitedRankName
from InDelegation
join rank ldr on LeaderRank = ldr.RankID
join rank inv on InvitedRank = inv.RankID

ASKER CERTIFIED SOLUTION

BillAn1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

davee_jay

Thanks for simplifying things BillAn1!

With the correlated sub-query solution I am thinking that we couldn't be sure about how many rows there were in the Rank table for a given InDelegation record (matching on either f_key) and therefore might actually return more than one row for a given InDelegation record - not, I believe what was required.

I guess this is down to interpretation of the brief at the end of the day!

BillAn1

They are foreign keys, by definition they will link to only 1 record in the Rank table (and RankID is the primary key)- so you don't need to worry about multiple rows.

software22

ASKER

Thanks BillAn1 for your help. I tried and it works :D.
I will ask another question tomorrow. Bye.

software22

ASKER

@davee_jay: I think your Correlated Subqueries is correct, it retuned same result but fk will only return 1 row so you do not need to use top 1. Sorry for my bad English. :D

Thanks.

PS: I have a another question at below before but no more help provided. Please hlp me in urgent !
https://www.experts-exchange.com/questions/21122470/How-to-create-dynamic-view.html

Let me know if I have to describe more for that question.