CODEWITHSUNDEEP
c#sqlms-access
B.K.
B.K.

Reputation: 10152

Join two tables where second table is used as a reference for multiple columns of the first table

I have two tables (names are fictitious for this example):

---------------------------------------
               MainTable
---------------------------------------
Condition_1 | Condition_2 | Condition_3
---------------------------------------
    J       |      H      |     N
    R       |      T      |     
    I       |             |     
    W       |      T      |     
    R       |      M      |     Q

...and so on...


--------------------------------------
              Conditions
--------------------------------------
Condition_Code | Condition_Description
--------------------------------------
      A        | Description goes here
      B        | Description goes here
      C        | Description goes here
      D        | Description goes here
      E        | Description goes here
      F        | Description goes here
      G        | Description goes here

...and so on...

I'd like to be able to query a DataSet that would have the Condition_Description from the Conditions table as the value of each condition that has a code within the MainTable table.

So that when I look at a specific row:

ds.Tables["Query"].Rows[i]["Condition_1"].ToString();

I would either get an empty string or a description of that condition, if it previously contained a code.

So that for the first row in the above example, it would return the full description of J instead of the character value.

I tried this query:

SELECT * FROM MainTable
    LEFT JOIN Conditions AS C1 ON
        MainTable.Condition_1 = C1.Condition_Code
    LEFT JOIN Conditions AS C2 ON
        MainTable.Condition_2 = C2.Condition_Code
    LEFT JOIN Conditions AS C3 ON
        MainTable.Condition_3 = C3.Condition_Code

...however, I get a missing operator in query expression error.

P.S. It was tough to create the title for this one, since my database experience is limited, so if anyone has a better title, feel free to change it.

Upvotes: 0

Views: 100

Answers (1)

Renatas M.
Renatas M.

Reputation: 11820

If I understood you correctly than maybe something like this would do the trick:

SELECT 
  (
    SELECT FIRST(Condition_Description) FROM Conditions C1 WHERE MT.Condition_1 = C1.Condition_Code  
  ) as C1_Description,
  (
    SELECT FIRST(Condition_Description) FROM Conditions C2 WHERE MT.Condition_2 = C2.Condition_Code  
  ) as C2_Description,
  (
    SELECT FIRST(Condition_Description) FROM Conditions C3 WHERE MT.Condition_3 = C3.Condition_Code  
  ) as C3_Description
FROM MainTable as MT

P.s. Not sure what is syntax of first function in access. Also Conditions table design is not very elegant :)

Upvotes: 1

Related Questions