0

First off, I am pretty sure my answer is here: SQL Inner-join with 3 tables?

but after two weeks, I give up.

  • Table AssemLines Fields ID, AssemID, ItemID, ItemQty, ServiceID, ServiceQty

  • Table Items Fields ItemID, ItemName, ItemDesc

  • Table Services Fields ServiceID, ServiceName, ServiceDesc

AssemLines sample data:

ID   -   AssemID   -   ItemID  -   ItemQty -  ServiceID   -  ServiceQty
1           1            12          102                        
2           1            62          15                              
3           1                                    3              45
4           2                                    6              90
5           2            23           5

Desired Query result:

AssemID   -   ItemName     -   ItemQty     -   ServiceName    -   ServiceQty
1               2" tube         102                          
1               3" tube         15
1                                                 Weld               45
2                                                 Saw                90
2               1" tube          5

Any ideas?

Community
  • 1
  • 1
Jason Ebright
  • 17
  • 6
  • Can you edit this to remove the `-----` so the columns align properly with the fixed-width font? I don't want to make assumptions about what columns are null/empty and it is quite difficult to tell what's what right now. Afterward, highlight and ctl-k or use the `{}` toolbar button to format back to fixed width. – Michael Berkowski Jun 16 '15 at 01:01

1 Answers1

0

It seems you just want to replace id's with proper names looked up in other tables. So the most basic join should work:

SELECT a.AssemID, i.ItemName, a.ItemQty, s.ServiceName, a.ServiceQty
FROM AssemLines a
LEFT JOIN Items i
  ON a.ItemID = i.ItemID
LEFT JOIN Services s
  ON a.ServiceID = s.ServiceID
piotrm
  • 12,038
  • 4
  • 31
  • 28
  • Yes, I just want to pull the name at this point. For some reason the above results in Syntax error in FROM clause. I also tried [SELECT a.AssemID, i.ItemName, a.ItemQty, s.ServiceName, a.ServiceQty FROM AssemLines a INNER JOIN Items i ON a.ItemID = i.ItemID INNER JOIN Services s ON a.ServiceID = s.ServiceID;] Resulted in same error. Also, can't figure out how to format this comment like the above comment. – Jason Ebright Jun 16 '15 at 01:30
  • Ms Access just Says "Syntax error in From clause" looks like it highlights the first JOIN behind the message window. – Jason Ebright Jun 16 '15 at 01:42
  • I put it back exactly your way. Ms Access Says: Syntax error (missing operator) in query expression 'a.ItemID = i.ItemID LEFT JOIN Services s ON a.ServiceID = s.ServiceI'. Not sure why it cuts off that last "D" – Jason Ebright Jun 16 '15 at 01:57
  • You have tagged your question `mysql` which is not exactly the same thing as Ms Access. However this is standard SQL that should work in Ms Access too, just make sure you have the same table and column names. – piotrm Jun 16 '15 at 01:58
  • OK it works, just have to use the foremating found here: http://stackoverflow.com/questions/8581838/multiple-left-join-in-access – Jason Ebright Jun 16 '15 at 02:07