multi join

[JoeriMJ]

I didn't know how to describe this problem so i took a shot with multi join. I'll give you the scenario.

Table: Products
-> exists of many fields, 2 of them should be lead_plant and other_plant.

Table: Plants
-> contains: plant_id and plant_name

In the products table i'd like to make a join between the lead_plant and other_plant by the plant_id.

How do I do this in the relationship window and what do I do best when using querys? Because in the end, I'd like to see the bound plant_name and not the plant_id when I create a report.

Greetz,
Joeri

 

[JerryKlmns]

SELECT P.*,
P1.plant_name As TheLeadPlant,
P2.plant_name As ThePlant
FROM (Products As P Inner Join Plants As P1 On
P.lead_plant = P1.Plant_id ) INNER JOIN Plants As P2 On
P.other_plant = P1.Plant_id;


I guess fields lead_plant and other_plant of table Products, contain the Plant_id value and not the plant_name value of table Plants.

 

[JoeriMJ]

yes they contain the ID and not the name.

Thanks for the query. it's great.
Do I have to add a relationship in the relationships window to make this work or is this unnecessairy ?

Greetz,
Joeri

 

[BobRodes]

If you want to use the relationships window, that's fine. Perhaps it will help to think of it this way. If you want to have two different lookup tables in relationship to a table, you can, right? All you do is have two different foreign keys (a "foriegn key" is a field which is a primary key in another table), one for each lookup table. Here, you have two different foreign keys, but they're relating to the same table. The fact that they are relating to the same table is irrelevant to Access; it's quite happy with the arrangement.

HTH

Bob

 

[JoeriMJ]

Yea, i was using two foreign keys linked to the same primary key. but it looked weird to begin with and I had trouble writing the query. Now it all came back to me when Jerry showed me that query. Not so hard, but this kind of SQL was a long time since I wrote something like it.
Thanks all

Greetz,
Joeri