Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Joins

Author  Topic 

Vergy39
Starting Member

15 Posts

Posted - 2012-12-24 : 17:46:37
Merry Christmas everyone. I have a question regarding Joins. I have a 3 tables in a database. One that has most data and utilizes ID's that are named in other tables. For example: TableData consists of columns of ConfirmationNumber, AssociateNumber, ReasonID, ApprovedByAssociateNumber, UpdatedByAssociateNumber. I need to join the tables that identify the ReasonID, AssociateNumber, ApprovedByAssociateNumber and the UpdatedByAssociateNumber. They are identified on tables called AssociateInfo, and ReasonInfo. The reason one is easy however, the assoiateinfo one is hard because the AssociateName and UpdateByAssociateNumber are to utilize the same column from the AssociateInfo table. If I join that table twice, such as :
Use MainDatabase
Select ConfirmationNumber, a.AssociateNumber, b.NameFirst AS AssocFrstName, b.NameLast AS AssocLstName, c.ReasonName, d.ApprovedByAssociateNumber, d.NameFirst AS ApprvdByFirstname, d.NameLast AS apprvdByLastName
From TableData a
INNER JOIN tbl.AssociateInfo b ON a.AssociateNumber = b.AssociateNumber
INNER JOIN tbl.AssociateInfo c On a.ApprovedByAssociateNumber = c.AssociateNumber

Hope this helps.

Thanks
David V

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-25 : 09:08:33
This looks like quite simple but your explanation seems confusing. Can you post Sample Data and Expected Output?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-26 : 00:02:56
sounds like a self join to me which is what you're using too. So didnt understand issue you're facing. What happened when you used posted query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Vergy39
Starting Member

15 Posts

Posted - 2013-01-23 : 16:59:25
Well the Holidays are over and I am back trying to make this work again. I also have another situation where this will help involving a query that returns the name of an associate, their immediate supervisor, and their manager. All information is on one table, but only the names of the associates are paired with the associateID. in order to get their immediate supervisor, I have the immediate supervisors assocID which I need to match up to the name. Is there a way to send an attachment that shows the data in an excel sheet? Maybe that would help. Let me know.

Thanks
David V
Go to Top of Page
   

- Advertisement -