How to join 3 tables in MS access

Question

I have 3 Tables

Item, Primary, Secondary

i want it to return All columns on Items and Brandname from Primary and Size, Color from Secondary

ItemID - pk(item) ItemID - fk(Primary) ItemID - fk(Secondary)

i know how to do 2 tables but I'm having problem on how to do 3 tables

here's my code

From Item, Primary, Secondary
Where Item.ItemID=Primary.ItemID AND Item.ItemID=Secondary.ItemID

can someone point me out on my mistakes

just notice Primary is color blue is this a reserved words from access?

Answer 1

Since primary is a reserved word, surround the word primary with brackets []

[Edit to explain comment] Using the query that @janet wrote, try adding an open paren before the item table and a closing paren just before the second inner join.

SELECT a.*, b.Brandname, c.Size, c.Color
FROM (Item a
    INNER JOIN [Primary] b ON a.ItemID = b.ItemID)
    INNER JOIN [Secondary] c ON b.ItemID = c.ItemID

Answer 2

try this:

From Item 
  JOIN [Primary] ON Item.ItemID=[Primary].ItemID
  JOIN [Secondary] ON Item.ItemID=[Secondary].ItemID

Answer 3

You can use this query

SELECT a.*, b.Brandname, c.Size, c.Color
FROM Item a
INNER JOIN Primary b ON a.ItemID = b.ItemID
INNER JOIN Secondary c ON b.ItemID = c.ItemID

The a, b and c are aliases for the tables in the query. a.* will return all the columns from Items table.

Answer 4

You should try

SELECT Item.*, Primary.Brandname, Secondary.Size, Secondary.Color
FROM Item 
inner join Primary  ON a.primary_id = Primary.id
inner join Secondary  ON Primary.secondary_id = Secondary.id