CODEWITHSUNDEEP
sqlms-access
blackmaler
blackmaler

Reputation: 119

How to join 3 tables in MS access

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?

Upvotes: 0

Views: 1664

Answers (4)

maxtwoknight
maxtwoknight

Reputation: 5346

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

Upvotes: 3

janet
janet

Reputation: 43

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

Upvotes: 0

BWS
BWS

Reputation: 3836

try this:

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

Upvotes: 1

Schalk
Schalk

Reputation: 142

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.

Upvotes: 0

Related Questions