?
Solved

How to do self join?

Posted on 2007-09-27
7
Medium Priority
?
196 Views
Last Modified: 2010-03-19
Table: Assets (AssetID, ReplacedByAssetID, PartNumber)
Table: Products(PartNumber, PartDesc)
ReplacedByAssetID allowed nulls. It may or may not have the value (int)
How do I construct a query to display:

AssetID, PartNumber, ReplacedByAssetID, ReplacedByPartNumber, ReplacedByPartDesc

0
Comment
Question by:chuang4630
7 Comments
 
LVL 1

Expert Comment

by:PamelaFluente
ID: 19975919
Here is a simple example.
http://www.technicalinterview.info/what-is-a-self-join-explain-it-with-an-example/

you just join a table with itself, by using an alias.

If you want to do it through a graphic ide (free) you can download this program:
http://www.datatime.eu/download.aspx
(a visual query builder)

-Pam




0
 
LVL 15

Expert Comment

by:dbbishop
ID: 19975931
SELECT     a.AssetID, a.ReplacedByAssetID, ra.PartNumber, p.PartDesc
FROM         Assets ra
INNER JOIN
    Assets a ON ra.AssetID = a.ReplacedByAssetID
INNER JOIN
    Products p ON ra.PartNumber = p.PartNumber
0
 
LVL 14

Expert Comment

by:Jai S
ID: 19976022
Table: Assets (AssetID, ReplacedByAssetID, PartNumber)
Table: Products(PartNumber, PartDesc)
ReplacedByAssetID allowed nulls. It may or may not have the value (int)
How do I construct a query to display:

AssetID, PartNumber, ReplacedByAssetID, ReplacedByPartNumber, ReplacedByPartDesc

SELECT A.ASsetID, A.PartNumber,RA.ReplacedAssetID,P.PartDesc
FROm Assets A
INNERT JOIN Asset RA ON Ra.AssetID = RA.ReplacedAssetID
INNER JOIN Products P ON P.PartNumer = A.PartNumber
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:David Todd
ID: 19976034
Hi,

... should this be ?
SELECT
  A.ASsetID
  , A.PartNumber
  , RA.ReplacedAssetID
  , RA.PartNumber
  , P.PartDesc
FROm Assets A
INNERT JOIN Asset RA ON RA.AssetID = A.ReplacedAssetID
INNER JOIN Products P ON P.PartNumer = RA.PartNumber

Regards
  David
0
 
LVL 1

Author Comment

by:chuang4630
ID: 19979105
Can we do the left join?

SELECT
  A.ASsetID
  , A.PartNumber
  , RA.ReplacedAssetID
  , RA.PartNumber
  , P.PartDesc
FROm Assets A
left JOIN Asset RA ON RA.AssetID = A.ReplacedAssetID
INNER JOIN Products P ON P.PartNumer = RA.PartNumber
0
 
LVL 35

Expert Comment

by:David Todd
ID: 19981048
Hi,

>> Can we do the left join?

Certainly.

What results do you get?

Regards
  David
0
 
LVL 15

Accepted Solution

by:
dbbishop earned 1500 total points
ID: 19981098
A LEFT JOIN should only return AssetID and PartNumber (original) if there is no ReplacedAssetID. It will return NULL for the other columns.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 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