How can i make INNER JOIN ON Table with two different tables SQL

Question

i have three tables ITEM_UNT , ITEM, UNIT How can i make INNER JOIN for ITEM_UNT WITH ITEM AND UNIT I used this statement and doesn't work :

SELECT
  ITEM_UNT.ITEM_NO, ITEM_UNT.ITEM_ID,ITEM_UNT.UNIT_NO,UNIT.UNIT_NO,
  UNIT.UNIT_NAME,ITEM.ITEM_ID, ITEM.ITEM_NAME
FROM ITEM_UNT,UNIT
INNER JOIN UNIT ON UNIT.UNIT_NO =  ITEM_UNT.UNIT_NO 
INNER JOIN ITEM ON ITEM.ITEM_ID =  ITEM_UNT.ITEM_ID

Which error are you actually getting? Please edit your question to add that information. — GMB, Sep 19 '19 at 14:17

score 0 · Answer 1 · answered Sep 19 '19 at 14:18

0

You have the Unit table twice with no alias... try

SELECT ITEM_UNT.ITEM_NO, ITEM_UNT.ITEM_ID,ITEM_UNT.UNIT_NO,UNIT.UNIT_NO, UNIT.UNIT_NAME,ITEM.ITEM_ID, ITEM.ITEM_NAME
FROM ITEM_UNT
INNER JOIN UNIT ON UNIT.UNIT_NO =  ITEM_UNT.UNIT_NO 
INNER JOIN ITEM ON ITEM.ITEM_ID =  ITEM_UNT.ITEM_ID

answered Sep 19 '19 at 14:18

HereGoes

1,302
1
9
14

you doesn't change in my statement – Borkan Sep 19 '19 at 14:27
Yes, in your question you have UNIT next to ITEM_UNT , I took that out. Also, do you have an ITEM_NO on your table ITEM_UNT table as well as ITEM_ID? – HereGoes Sep 19 '19 at 14:30
yes i have ITEM_NO in ITEM_UNT as a PK and I have also ITEM_ID in ITEM_UNT as FK which is a PK in ITEM Table – Borkan Sep 19 '19 at 14:33

score 0 · Answer 2 · answered Sep 19 '19 at 14:35

0

Please try the below :

Option 1:

select iu.item_no,
       iu.item_id,
       iu.unit_no,
       u.unit_no,
       u.unit_name,
       i.item_id,
       i.item_name
from   item_unt iu
inner  join unit u
on     u.unit_no = iu.unit_no
inner  join item i
on     i.item_id = iu.item_id;

Option 2:

select iu.item_no,
       iu.item_id,
       iu.unit_no,
       u.unit_no,
       u.unit_name,
       i.item_id,
       i.item_name
from   item_unt iu,
       unit     u,
       item     i
where  u.unit_no = iu.unit_no
and    i.item_id = iu.item_id;

Share some details of each table as well for better understanding the issue.

answered Sep 19 '19 at 14:35

San

1

the option 1 doesn't work but the option 2 it works fine – Borkan Sep 19 '19 at 14:43
Option 2 is old style join and should be avoided, just check your syntax and tells us what error message you got. Promote the use of explict `JOIN` sintaxis, Aaron Bertrand wrote a nice article [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) about it. – Juan Carlos Oropeza Sep 19 '19 at 14:45
In access you need to use parenthesis for each join. check here https://learn.microsoft.com/en-us/office/vba/access/concepts/structured-query-language/perform-joins-using-access-sql – Juan Carlos Oropeza Sep 19 '19 at 14:46
the error is : The operator is missing inner join unit u on u.unit_no = iu.unit_no inner join item i on i.item_id = iu.item_id; @JuanCarlosOropeza – Borkan Sep 19 '19 at 14:52
As @Juan said I think you need to use `FROM (item_unt iu INNER JOIN unit u ON u.unit_no = iu.unit_no) INNER JOIN item i ON i.item_id = iu.item_id` for the JOIN version. – Darren Bartrup-Cook Sep 19 '19 at 14:58
thanks @DarrenBartrup-Cook but if i want to join another table how can i add it ? – Borkan Sep 19 '19 at 15:04
@Borkan below will be the syntax for multiple tables: select iu.item_no, iu.item_id, iu.unit_no, u.unit_no, u.unit_name, i.item_id, i.item_name from ((item_unt iu inner join unit u on u.unit_no = iu.unit_no) inner join item i on i.item_id = iu.item_id) inner join tablex; More details at : https://stackoverflow.com/questions/19367565/access-sql-inner-join-with-multiple-tables – San Sep 19 '19 at 15:10
Just add the table as another join and put the brackets in - `FROM ((item_unt iu INNER JOIN unit u ON u.unit_no = iu.unit_no) INNER JOIN item i ON i.item_id = iu.item_id) INNER JOIN anothertable at ON anothertable.foreign_key = iu.item_id`. Note - 2 brackets after FROM which close before each new join. – Darren Bartrup-Cook Sep 19 '19 at 15:12

score 0 · Answer 3 · answered Sep 19 '19 at 16:53

You should not mix the comma separated list of tables and the explicit JOIN clause

SELECT  ITEM_UNT.ITEM_NO
      , ITEM_UNT.ITEM_ID
      , ITEM_UNT.UNIT_NO
      , UNIT.UNIT_NO
      , UNIT.UNIT_NAME
      , ITEM.ITEM_ID
      , ITEM.ITEM_NAME
FROM ITEM_UNT
INNER JOIN UNIT ON UNIT.UNIT_NO =  ITEM_UNT.UNIT_NO 
INNER JOIN ITEM ON ITEM.ITEM_ID =  ITEM_UNT.ITEM_ID

score 0 · Accepted Answer · answered Sep 19 '19 at 17:04

I have no idea what "not work" means, but you can at least write the query according to MS Access query syntax. That would look like:

SELECT ITEM_UNT.ITEM_NO, ITEM_UNT.ITEM_ID, ITEM_UNT.UNIT_NO, UNIT.UNIT_NO,
       UNIT.UNIT_NAME, ITEM.ITEM_ID, ITEM.ITEM_NAME
FROM (ITEM_UNT INNER JOIN
      UNIT
      ON UNIT.UNIT_NO =  ITEM_UNT.UNIT_NO 
     ) INNER JOIN
     ITEM
     ON ITEM.ITEM_ID =  ITEM_UNT.ITEM_ID;

I also removed the repeated ITEM reference.

How can i make INNER JOIN ON Table with two different tables SQL

4 Answers4

Option 1:

Option 2: