Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

multi join

Status
Not open for further replies.

JoeriMJ

Programmer
Aug 9, 2007
36
BE
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
 

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.
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top