asked on

LEFT JOIN twice ...

Hello,
Please excuse my poor english. Here is my problem :

Database name = "MainDB"
The main table, named "Main", is made of 3 fields : "Product" , "InStock" and "Missing"

Product InStock Missing
--------------------------------------
A 2 4
B 7 2
...

A secondary table named "Translation" is made of 2 fields : "Value" and "Text"

Value English
---------------------
1 one
2 two
3 three
...

I want the following result on the screen, i.e. translating values to other ones thanks to the "Translation" table. Please note that it is only a sample, I have not only to translate such easy terms !!!

Product InStock Missing
--------------------------------------
A two four
B seven two
...

Here is what I have written :
Dim Ds as Recordset
Set Ds = MainDB.OpenRecordset("SELECT Main.Product, Translation.Text AS A, Translation.Text AS B FROM Main LEFT JOIN (Main LEFT JOIN Translation ON Main.InStock = Translation.Value) ON Main.Missing = Translation.Value) WHERE Product ...", 4)

But is doesn't work ...

Thank you for your help !
Philippe.

Ryan Chong

Try this:

SELECT id, Product ,
(Select top 1 English From Translation Where Translation.Value = Main.InStock) As InStock ,
(Select top 1 English From Translation Where Translation.Value = Main.Missing) As Missing
From Main

Hope this helps

ASKER CERTIFIED SOLUTION

p_sie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MSelect

ASKER

Thank you very much and your remark was really true : using "SELECT Top" was VERY VERY slow, even to get only 3 or 4 records ... I had even thought that my computer had crashed :-)

p_sie

Your welcome!