asked on

how to create an inner join

I am using Microsoft Query to query two table and return the data into Excel. I went to w3school.com to learn how to create an inner join. When I followed the instructions I received a syntax error. When I used MS Query's visual editor it generated the code I have in the code snippet box. Listed below are my two tables.

CGEQUP_All
InventoryStatus
StockNo

CGNOTE
Note
NoteSegNo
StockNo

This is the example that w3schools gives:
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Does this syntax not work with MS Query or Excel? It seems to be using a where statement instead of inner join.

select CGEQUP_All.StockNo,
       CGNOTE.Note
from   JDIS_Data_files.CGEQUP_All CGEQUP_All,
       JDIS_Data_files.CGNOTE CGNOTE
where  CGNOTE.StockNo = CGEQUP_All.StockNo
   and
       (
              (
                     CGEQUP_All.InventoryStatus='H'
              )
          and
              (
                     CGNOTE.NoteSeqNo>0
              )
           or
              (
                     CGEQUP_All.InventoryStatus='R'
              )
          and
              (
                     CGNOTE.NoteSeqNo>0
              )
           or
              (
                     CGEQUP_All.InventoryStatus='I'
              )
          and
              (
                     CGNOTE.NoteSeqNo>0
              )
       )

Open in new window

ASKER CERTIFIED SOLUTION

PaulKeating

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

UnifiedIS

The first condition in the where clause essentially inner joins the tables on StockNo but different applications handle SQL differently. In T-SQL, I would write it like this:
SELECT CGEQUP_All.StockNo, CGNOTE.Note
FROM JDIS_Data_files.CGEQUP_All
INNER JOIN JDIS_Data_files.CGNOTE
ON CGNOTE.StockNo = CGEQUP_All.StockNo
WHERE CGEQUP_All.InventoryStatus IN ('H', 'R', 'I')
AND CGNOTE.NoteSeqNo > 0

Majikthise

ASKER

Thank you!