get column data from another Table

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 2005 Forums

Transact-SQL (2005)

get column data from another Table

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2010-12-26 : 06:40:41

Hello,

I have 2 tables

1.Departments
column name :ID, DeptID,DeptName

2.Documents
ColumnName: ID, DeptID, DocName,DocType

Now
This Is my Query

SELECT * FROM Documents

I want DeptName instead of DeptID in Results

Regards

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-12-26 : 07:07:57

Try this:


SELECT Doc.ID,
       (SELECT Dep.DeptName
          FROM Departments AS Dep
         WHERE Dep.DeptID = Doc.DeptID) AS DeptName,
       Doc.DocName,
       Doc.DocType
  FROM Documents AS Doc

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-26 : 08:09:46

Why write such a complex form for a beginner?
The simplest form is a straightforward 2-table join, no subqueries necessary.

SELECT Doc.ID,
       Dept.DeptName,
       Doc.DocName,
       Doc.DocType
  FROM dbo.Documents AS Doc
    INNER JOIN dbo.Departments AS Dep ON Dep.DeptID = Doc.DeptID

--
Gail Shaw
SQL Server MVP

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-12-28 : 19:46:14

quote:
Originally posted by GilaMonster

Why write such a complex form for a beginner?
The simplest form is a straightforward 2-table join, no subqueries necessary.

SELECT Doc.ID,
Dept.DeptName,
Doc.DocName,
Doc.DocType
FROM dbo.Documents AS Doc
INNER JOIN dbo.Departments AS Dep ON Dep.DeptID = Doc.DeptID

--
Gail Shaw
SQL Server MVP

In my opinion LEFT/INNER JOIN is harder to understand for a beginner than sub-query, and I think you should use LEFT OUTER JOIN in your query instead of INNER JOIN.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-29 : 04:28:50

I disagree. Joins are not hard, they don't break unexpectedly when subqueries do, and there can be performance benefits as well. In short, learn to do it right the first time, not at a later date.

Why should I use Left Outer Join?
There's a DeptID column in the Docs table. Unless someone messed up the referential integrity, left out a foreign key and then deleted the referenced row from the Department table, there's absolutely no reason to think there won't be a matching row in the Departments table.
Whether the join should be to Dept.ID or Dept.DeptID is another matter...

--
Gail Shaw
SQL Server MVP

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-12-29 : 16:25:25

quote:
Originally posted by GilaMonster

I disagree. Joins are not hard, they don't break unexpectedly when subqueries do, and there can be performance benefits as well. In short, learn to do it right the first time, not at a later date.

Why should I use Left Outer Join?
There's a DeptID column in the Docs table. Unless someone messed up the referential integrity, left out a foreign key and then deleted the referenced row from the Department table, there's absolutely no reason to think there won't be a matching row in the Departments table.
Whether the join should be to Dept.ID or Dept.DeptID is another matter...

--
Gail Shaw
SQL Server MVP

At first regarding (sub-query vs join) that is your opinion, and I respect it. But in my opinion it is simpler, nicer and down to the question to use sub-query. So let us agree to disagree.

Secondly regarding why we should use LEFT OUTER JOIN? I think it is an obvious that this the safer option right here. Unless it is acceptable to eliminate Documents that either have null DeptID or their values do not match any Department. Your point of view is right if we know for sure that the appropriate DRI constraints exist, and in my opinion I will not risk nor ask a beginner to check them. So I will go with the safer option here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources