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
1 2036
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 - declare @t table(
-
ID int identity
-
,Partnumber varchar(100)
-
,Replacment varchar(100)
-
,Description varchar(100)
-
,Comment varchar(100))
-
insert into @t
-
select '5030331-001','Part 1',null,null union all
-
select '5030332-001','5030332-002','Part1 from vendor 1','Obsolete' union all
-
select '5030332-002','8063737-001','Part1 from vendor 1','Obsolete' union all
-
select '5030333-001','Part 4',null,null union all
-
select '8063737-001',null,'Part1 from vendor 2',null
The output of the sample table is - ID Partnumber Replacment Description Comment
-
1 5030331-001 Part 1 NULL NULL
-
2 5030332-001 5030332-002 Part1 from vendor 1 Obsolete
-
3 5030332-002 8063737-001 Part1 from vendor 1 Obsolete
-
4 5030333-001 Part 4 NULL NULL
-
5 8063737-001 NULL Part1 from vendor 2 NULL
Based on the above table structure, here is the program - ;with cte as
-
(
-
select id,Partnumber,Replacment,Description,Comment
-
from @t
-
where Partnumber = '8063737-001' -- Enter the partnumber here
-
union all
-
select t.id,t.Partnumber,t.Replacment,t.Description,t.Comment
-
from @t t
-
inner join
-
cte c
-
on c.Partnumber = t.Replacment
-
)
-
-
select * from cte
-
order by id
-
option (maxrecursion 0)
And the output is: - id Partnumber Replacment Description Comment
-
2 5030332-001 5030332-002 Part1 from vendor 1 Obsolete
-
3 5030332-002 8063737-001 Part1 from vendor 1 Obsolete
-
5 8063737-001 NULL Part1 from vendor 2 NULL
Hope this helps.
Let me know in case of any concern
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Robert Marshall |
last post by:
Hi all,
Hoping someone can help me with an issue I'm having.
I'm using AccessXP to create a database of U.S. Census records. After
keying the data in, I decided to run a query to create a...
|
by: deko |
last post by:
If I release a new version of my mbd (in mde format) and users want to
upgrade - how do they migrate their data?
For example, if the original was released as data1.mde and then I release...
|
by: pw |
last post by:
Hi,
I am having a mental block trying to figure out how to code this.
Two tables:
"tblQuestions" (fields = quesnum, questype, question)
"tblAnswers" (fields = clientnum, quesnum, questype,...
|
by: Apple |
last post by:
1. I want to create an autonumber, my requirement is : 2005/0001
(Year/autonumber), which year & autonumber no. both can auto run.
2. I had create a query by making relation to a table & query,...
|
by: Fendi Baba |
last post by:
I created a person table with various fields such as Suffix,
Salutation, etc, Some of these fields may not be mandatory for example
suffix. In the actual table itself, I only have a field for...
|
by: themastertaylor |
last post by:
I've got a system to manage various quotes for building materials for
a number of sites. i want a query to produce a report that shows me
who HASN'T quoted for which sites. basically so i can...
|
by: wugon.net |
last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad
query performance
Env:
db2 LUW V8 + FP14
Problem :
We have history data from 2005/01/01 ~ 2007/05/xx in single big...
|
by: Miro |
last post by:
VS2008
I have created 3 tables.
Vendors
Customers
PhoneNumbers
each have their own key
Vendor has: VendorID - int unique identifier
Customer has: CustomerID - int unique identifier
|
by: LudoS |
last post by:
Hi All, I hope someone is able to help me with this.
I am having a table with partnumber(s) like shown here
ID (autonumber, primary key)
Partnumber (text)
Replacment (text)
Description...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |