Solved

Join double column in a table, urgent

Posted on 2004-09-11
7
271 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:software22
  • 3
  • 2
  • 2
7 Comments
 
LVL 1

Expert Comment

by:davee_jay
ID: 12033622
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
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12033639
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
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 500 total points
ID: 12033647
of course that should have read :
select  d.InDelegationID, d.InDelegationName, ldr.name AS LeaderRankName, inv.name AS InvitedRankName
from InDelegation  d
join rank ldr on LeaderRank = ldr.RankID
join rank inv on InvitedRank = inv.RankID
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 1

Expert Comment

by:davee_jay
ID: 12033667
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!

0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12033713
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.
0
 

Author Comment

by:software22
ID: 12034248
Thanks BillAn1 for your help. I tried and it works :D.
I will ask another question tomorrow. Bye.
0
 

Author Comment

by:software22
ID: 12034320
@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 !
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21122470.html

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now