+8 votes
by (13.6m points)

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?

4 Answers

+5 votes
by (13.6m points)

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
+3 votes
by (13.6m points)

try this:

From Item 
  JOIN [Primary] ON Item.ItemID=[Primary].ItemID
  JOIN [Secondary] ON Item.ItemID=[Secondary].ItemID
+3 votes
by (13.6m points)

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.

0 votes
by (13.6m points)

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

Related questions

+13 votes
2 answers
+5 votes
3 answers
asked 5 hours ago by wyhgood (13.6m points)
+15 votes
3 answers
+15 votes
2 answers
...