By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

query for relation between 2 fields

P: 3
Hi All, I hope someone is able to help me with this.

I am having a table with partnumber(s) like shown here (actually a access2003 database)

ID (autonumber, primary key)
Partnumber (text)
Replacment (text)
Description (text)
Comment (text)
..... <Some other fields>.

Data look like this:
ID Partnumber Replacement Description Comment
x 5030331-001 Partx
x 5030332-001 5030332-002 Part1 from vendor 1 Obsolete
x 5030332-002 8063737-001 Part1 from vendor 1 Obsolete
x 5030333-001 Partx
x .....
x 8063737-001 Part1 from vendor2

There are a lot of Partnumbers which are obsolete (this partnumber has a replacement part). I am searching for a query where (with one parameter, = partnumber to look for), where i can see complete relation. So if I am searching for partnumber 8063737-001 I get same result as when I am searching for 5030332-001 or 5030332-002.

Result should be like this:
ID Partnumber Replacment Description Comment ...........
x 5030332-001 5030332-002 Part1 from vendor 1 Obsolete
x 5030332-002 8063737-001 Part1 from vendor 1 Obsolete
x 8063737-001 Part1 from vendor2


has anyone any idea how to create this kind of query?

Ludo
Nov 22 '09 #1
Share this Question
Share on Google+
1 Reply


nbiswas
100+
P: 149
Hi,
I have made a table populated with some data based on what I have understood.
Then I am using a recursive CTE for fetching the result.
I am using SQL SERVER 2005+

Test the test table first and include the appropriate data over there and then run the query.

--Table creation and sample data population

Expand|Select|Wrap|Line Numbers
  1. declare @t table(
  2.     ID int identity
  3.     ,Partnumber  varchar(100)
  4.     ,Replacment varchar(100)
  5.     ,Description varchar(100)
  6.     ,Comment varchar(100))
  7. insert into @t
  8.     select '5030331-001','Part 1',null,null union all
  9.     select '5030332-001','5030332-002','Part1 from vendor 1','Obsolete' union all
  10.     select '5030332-002','8063737-001','Part1 from vendor 1','Obsolete' union all
  11.     select '5030333-001','Part 4',null,null union all
  12.     select '8063737-001',null,'Part1 from vendor 2',null
The output of the sample table is
Expand|Select|Wrap|Line Numbers
  1. ID    Partnumber    Replacment    Description    Comment
  2. 1    5030331-001    Part 1    NULL    NULL
  3. 2    5030332-001    5030332-002    Part1 from vendor 1    Obsolete
  4. 3    5030332-002    8063737-001    Part1 from vendor 1    Obsolete
  5. 4    5030333-001    Part 4    NULL    NULL
  6. 5    8063737-001    NULL    Part1 from vendor 2    NULL
Based on the above table structure, here is the program

Expand|Select|Wrap|Line Numbers
  1. ;with cte as
  2. (
  3.     select id,Partnumber,Replacment,Description,Comment
  4.     from @t
  5.     where Partnumber = '8063737-001' -- Enter the partnumber here
  6.     union all
  7.     select t.id,t.Partnumber,t.Replacment,t.Description,t.Comment
  8.     from @t t
  9.     inner join 
  10.     cte c
  11.     on c.Partnumber = t.Replacment
  12. )
  13.  
  14. select * from cte
  15. order by id
  16. option (maxrecursion 0)
And the output is:

Expand|Select|Wrap|Line Numbers
  1. id    Partnumber    Replacment    Description    Comment
  2. 2    5030332-001    5030332-002    Part1 from vendor 1    Obsolete
  3. 3    5030332-002    8063737-001    Part1 from vendor 1    Obsolete
  4. 5    8063737-001    NULL    Part1 from vendor 2    NULL
Hope this helps.

Let me know in case of any concern
Nov 23 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.