[blyxx86]

Hello Everyone,

I am currently stuck on how the concept of a self-join works. At least in the format I want it.

I need to create a query that will show only Active parts. Parts are being replaced by new revisions and new models constantly.

I have one table:

Expand|Select|Wrap|Line Numbers

1. Field     Type     Null     Default     Links to     Comments     MIME
2. ModelID     int(10)     No                         
3. ModelName     varchar(25)     No                         
4. Discontinued     enum('TRUE', 'FALSE')     No      'FALSE'                   
5. ReplacedWithModelID     int(10)     Yes      NULL      tblmodel -> ModelID             
6. ModelImage     varchar(200)     Yes      NULL                   
7.  

As you can see ReplacedWithModelID looks up ModelID.

If I have a table with this data:

Expand|Select|Wrap|Line Numbers

1. ModelID  ModelName  ReplacedWithModelID
2. 1        ModelA
3. 2        ModelB        3
4. 3        ModelC        4
5. 4        ModelD
6.  

How would I create a query that has results like.

Expand|Select|Wrap|Line Numbers

1. ModelID  ModelName
2. 1        ModelA
3. 4        ModelD
4.  

If I have only one replacement I can create the query, but as the list of replacements gets longer 2 by 3, 3 by 4... I end up getting information that I do not want.

Expand|Select|Wrap|Line Numbers

1. SELECT DISTINCTROW tblModel.ModelName, tblModel.ModelID, tblModel.ReplacedWithModelID
2. FROM tblModel
3. WHERE (((tblModel.ReplacedWithModelID) Is Null)) OR (((tblModel.ModelID) In (SELECT tblModel.ReplacedWithModelID
4. FROM tblModel
5. WHERE (((tblModel.ReplacedWithModelID) Is Not Null)))));
6.  

Please help me to figure this out.

Thank you!

[r035198x]

So your results should contain only models that have not been replaced?
Why not just select where ReplacedWithModelID is not null?

If I didn't get your question correctly (as is most likely) then could you explain in plain words what your output should show.

[blyxx86]

Haha!
I saw that yesterday I could repeat the resultset if I have a WHERE clause of "NULL" but for some reason I feel like that doesn't show the data the right way.

I need it somehow replace old model info with the new model ID. For example if ID 2 Replaced ID 1, then I would want to update all references of ID 1 with ID 2. However, I do not want this query to change the data in the tables.

For example:

Expand|Select|Wrap|Line Numbers

1. SELECT tblModel.ModelID, tblModel.ModelName, tblModel.ReplacedWithModelID, tblModel.Discontinued, tblService.ServiceDate, tblService.ModelID
2. FROM tblModel INNER JOIN tblService ON tblModel.ModelID = tblService.ModelID;
3.  

Produces:

Expand|Select|Wrap|Line Numbers

1. --------------------------------------------------------------------------------------------------------------------------------------------------------------
2. |  tblModel.ModelID  |    ModelName    | ReplacedWithModelI |     Discontinued      |    ServiceDate     |                tblService.ModelID                 |
3. --------------------------------------------------------------------------------------------------------------------------------------------------------------
4. |                  2 | ModelA          |                  3 |                   Yes |           9/5/2008 | ModelA                                            |
5. --------------------------------------------------------------------------------------------------------------------------------------------------------------
6. |                  2 | ModelA          |                  3 |                   Yes |           9/5/2008 | ModelA                                            |
7. --------------------------------------------------------------------------------------------------------------------------------------------------------------
8. |                  3 | ModelB          |                  4 |                    No |           9/5/2008 | ModelB                                            |
9. --------------------------------------------------------------------------------------------------------------------------------------------------------------
10. |                  4 | ModelC          |                  5 |                    No |           9/5/2008 | ModelC                                            |
11. --------------------------------------------------------------------------------------------------------------------------------------------------------------
12. |                  5 | ModelD          |                    |                    No |           9/5/2008 | ModelD                                            |
13. --------------------------------------------------------------------------------------------------------------------------------------------------------------
14. |                  6 | ModelE          |                    |                    No |           9/5/2008 | ModelE                                            |
15. --------------------------------------------------------------------------------------------------------------------------------------------------------------
16.  
17.  

I somehow want the information of the tblService.ModelID to be changed (dynamically) to the ReplacedModel.

Any idea on this one?

[blyxx86]

Would this have to be done within the application itself in a loop until "ReplacedModel" is a null value?

I'm still a bit lost on how to accomplish my goal.