Database Query Question?

[jward]

I need to run a query on a database table that looks like this

Col1 Col2 Col3
_________________________
1325 001 3278
1265 001 2356
3278 001 3335
3335 001 3335F
3335F 001 33234

Notice how the 3rd column references the first column. So if I query 1325 I need to run another query against Col1
with the value returned from col3 and so on and The final result needs to be bound to a datagrid

So the query 1325 should return the folowing

Col1 Col2 Col3
________________________
1325 001 3278
3278 001 3335
3335 001 3335F
3335F 001 33234

This process needs to go several levels deep (10)

 

[PankajBanga]

Please be more precise. What are you trying to achieve?

 

[jward]

The table I am running the query on is a build of materials,
both column 1 and column 3 are part numbers. When you query column 1 for a component it can contain sub compnents. For instance lets say 1325 is a door assembly and I want to get
the sub components that make up that door assembly.Running the query for that I find in Column 3 that there is a sub component of 3278 which is lets say the door frame. Now I need to go back and query Column 1 for the 3278. Running that query returns a subcomponent of 3335. So lets say that is the actual door. So I need to then query Column 1 for 3335 and find that that is door handle. Now I look in Column 3 and find 3335F so I query Column 1 and find out that is the keys. Finally I find that Column 3 has 33234 so I query Column 1 and find that that is the key ring.

So basically I need to bounce between columns 1 and 3 build a datagrid based on all components referenced. This can go 10 levels deep, meaning sub components can have sub components.

The final result needs to be a datagrid with all the information returned for the component. The main component and all sub components.

 

[sunaj]

Hi,

You have made a circular reference, where the table rerences itself - there is nothing wrong with that, it's a very flexible structure, but it can be a little tricky to query.
You need to join the table with itself.
Here is an Example:

tblSelfRefID Data            tblSelfRefID_Parent 
------------ --------------- ------------------- 
1            Door Assembly   NULL
2            Door frame      1
3            Door            1
4            knob            2
5            Hinges          2
6            Hinge screws    5

select * from tblSelfRef S1 INNER JOIN tblSelfRef S2 ON S1.tblSelfRefID = S2.tblSelfRefID_Parent
Yields:

tblSelfRefID Data           ID_Parent tblSelfRefID Data          ID_Parent
------------ -------------- --------- ------------ ------------- --------- 
1            Door Assembly  NULL      2            Door frame    1
1            Door Assembly  NULL      3            Door          1
2            Door frame     1         4            knob          2
2            Door frame     1         5            Hinges        2
5            Hinges         2         6            Hinge screws  5

-Which I guess is close to what you want.
Good luck


Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[jward]

Sanaj,

I think your pointing me in the right direction however I can't seem to make it work. Below is a sample of the database I'm searching and then the results I need to see

CatalogueSC CatNumItem SubPartNum
------------ -------------- -------------
RMPP23F RMPP23F001 53807-23F
RMPP23F RMPP23F004 H148
RMPP23F RMPP23F005 H100Z
RMPP23F RMPP23F006 087458
RMPP23F RMPP23F007 10966M
RMPP23F RMPP23F002 GSPS078
RMPP23F RMPP23F003 H221

The select starts from RMPP23F and should return the following


CatalogueSC SubPartNum
------------ ----------------
RMPP23F NULL
RMPP23F 53807-23F
53807-23F 53807-23RAW
53807-23RAW CRS-14GA-48X96
53807-23F 8104-49206
RMPP23F GSPS078
RMPP23F H221
RMPP23F H148
RMPP23F H100Z
RMPP23F 087458
RMPP23F 10966M
10966M 10966X
10966X CRS-22GA-48X96
10966M 8P


Is this Possible and if so what would the Selection & inner Join statement look like
In the above example All I know is that the Component is
RMPP23F, I need to return every subcomponent associated with that component as well as every subcomponent associated with each subcomponent

I hope this makes sense

Thanks

 

[sunaj]

Hi agian,
You don't show all values in your table, but I've just relalized that you are referencing Child (sub) items and not Parent (sup?) in the table. THAT IS WRONG! Can you see that? Imagine that you had, say 2 (it could be more) tables linked to each other in a hierachy. You would want several rows in table2 to link to one row in table1 ('door frame' and 'door' links to 'door assembly'). e.i. a one to many relation. In order to do this you would put the ID of table1 in table2 to make up the foreign key. What you have done is the other way around, you have put the ID of table2 in table1 and therefore you need just as many rows in table1 as in tabel2 -one for each reference. You have made a 1-1 relation, which is meaningless.

I would strongly recommend you to redesign the database (reference the parent row, not the child). If that is not a possibility there's probably a solution to your problem anyway...

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[jward]

Thank's Sanaj,
The database is was pulled off a Midrange(AS/400) system and was originally designed several years ago. Now that system is going away and I have the task of giving our users viewing capability similar to what they had on the midrange. We have imported the data into MS SQL2000 and I don't believe redesigning the database is a option. Thanks again for your help

Joe