SQL for Lookup tables

[FreeriderUK]

In my Expenses table (Access DB), I have fields Category, Payee & Method

I also have a Category, Payee & Method table which are LOOKUP tables, all with ID and Description.

I need a SQL statement which will collect the descriptions of these three fields.

I can get the results from the Category table using the following:

SELECT ExpenseID, Categories.CategoryDescription
FROM (Categories INNER JOIN Expenses ON Expenses.ExpenseCategory = Categories.categoryid)

but, can't seem to get my head round multiple tables.

How can I achieve the equivalent of:

SELECT ExpenseID, Categories.CategoryDescription, ...
FROM (Categories INNER JOIN Expenses ON Expenses.ExpenseCategory = Categories.CategoryID)
(Payees INNER JOIN Expenses ON Expenses.ExpensePayee = Payees.PayeeID)
(Methods INNER JOIN Expenses ON Expenses.ExpenseMethod = Methods.MethodID)

?

 

[InertiaM]

SELECT ExpenseID, Categories.CategoryDescription, ...
FROM (Categories INNER JOIN Expenses ON Expenses.ExpenseCategory = Categories.CategoryID)
(Payees INNER JOIN Expenses ON Expenses.ExpensePayee = Payees.PayeeID)
(Methods INNER JOIN Expenses ON Expenses.ExpenseMethod = Methods.MethodID)

SELECT ExpenseID, Categories.CategoryDescription, ...
FROM
(
(
Categories INNER JOIN Expenses ON Expenses.ExpenseCategory = Categories.CategoryID
)
INNER JOIN Payees ON Expenses.ExpensePayee = Payees.PayeeID
)
INNER JOIN Methods ON Expenses.ExpenseMethod = Methods.MethodID

You obviously dont need the line spacing - I've just added it here to make it more legible.

 

[FreeriderUK]

Thanks InertiaM, that works perfectly.

Now to figure out why...

 

[cjard]

Your expenses table is the centre of a star, right, and the lookup tables join as fingers to it?

it will be more understandable if you write your queries betteR:

SELECT
  e.ID,
  c.CategoryText,
  p.PayeesText,
  m.MethodText
FROM
  Expenses e
  INNER JOIN
  Categories c 
  ON
    e.ExpenseCategory = Categories.CategoryID

  INNER JOIN
  Payees p
  ON 
    e.ExpensePayee = Payees.PayeeID

  INNER JOIN
  Methods m
  ON 
    e.ExpenseMethod = Methods.MethodID

Get it? you have ONE expenses table, and you stick 3 other tables to it. You do NOT write it out 3 times and attempt to stick one table each time