Can I (join) more than two tables/fields

[Guest_imported]

Hi,
I'm using ADO/MSaccess db.

Is it possible to do an 'INNER JOIN' across more than two tables? As you
might guess, I don't do much SQL so I just can't imagine how I would express
joining in another table/field.

SQL = "SELECT ITEM.[ItemIDnum], ITEM.[ItemName], VENDOR.[VendIDnum],
VENDOR.[Name] " _
& "FROM (VENDOR INNER JOIN LOOKUP ON VENDOR.[VendIDnum] =
LOOKUP.[VendIDnum]) " _
& "INNER JOIN ITEM ON LOOKUP.[ItemIDnum] = ITEM.[ItemIDnum] " _
& "WHERE ITEM.[ItemIDnum] = " & itemID & " " _
& "ORDER BY VENDOR.[Name];"

As you can see I have a number of tables, the three I need/want to join are
VENDOR(the company names etc.)
ITEM(keywords/categories)
COMMS(tel,email,

http://www etc.).

The above query joins the two tables successfully, however I want to add in
(join in) the COMMS.

http://www field.

Any advice would be most welcome (code to illustrate would be even more
welcome :0)

Best regards
Neil

 

[rac2]

Hello Neil,

There must be a column in common between COMMS and one of the other tables. Assume it has a column with VendorIDnum.

And what about LOOKUP? Is it a table that shows which vendors have which items?

If so, then this will yield names and URLs of vendors who supply the item.

SELECT VENDOR.Name, COMMS.www, ITEM.ItemName

FROM VENDOR
LEFT JOIN COMMS   ON VENDOR.VendorIDnum = COMMS.VendorIDnum
INNER JOIN LOOKUP ON VENDOR.VendorIDnum = LOOKUP.VendorIDnum
INNER JOIN ITEM   ON LOOKUP.ItemIDnum   = ITEM.ItemIDnum

WHERE ITEM.ItemIDnum = " & itemID & " "  & "ORDER BY VENDOR.Name

The LEFT JOIN insures that vendors without COMMS rows will be included.

I left off the square brackets since these are only needed with column names that contain spaces, e.g. [item ID num].

In general there is no limit to the number of tables that can be joined, only that there must be columns in common to use to join the tables.