Join table to itself - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Join table to itself

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-10-22 : 09:45:34

[code]
I have a Product structure table bmprdstr

item_no comp_item
ABC XYZ
XYZ 111
XYZ 222

I'm looking for a select statement where I can find all records where the comp_item is also in the item_no field.

So in the example above I would like the 2nd and 3rd row returned.

[/code]

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-22 : 09:51:38

SELECT item_no, comp_item
FROM bmprdstr
WHERE comp_item IN (SELECT distinct item_no from bmprdstr)

--
Chandu

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2012-10-22 : 10:19:59

SELECT A.item_no, A.comp_item
FROM bmprdstr A
INNER JOIN bmprdstr B
ON A.item_no = B.item_no

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-22 : 12:20:26

[code]
SELECT a.*
FROM bmprdstr b
INNER JOIN bmprdstr a
ON b.comp_item = a.item_no
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources