Solved

how to create an inner join

Posted on 2008-10-13
3
1,250 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:Majikthise
3 Comments
 
LVL 5

Accepted Solution

by:
PaulKeating earned 125 total points
Comment Utility
INNER JOIN is SQL-92 and is still not implemented in certain dialects of SQL. Besides which, in simple cases it's wordier than the old way, which is of course still legal. Recipe for translating a 2-table inner join into SQL-86:
1. replace INNER JOIN with a comma
2. replace ON with WHERE

So

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

can also be rendered

SELECT column_name(s)
FROM table_name1, table_name 2
WHERE table_name1.column_name=table_name2.column_name


0
 
LVL 18

Expert Comment

by:UnifiedIS
Comment Utility
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
0
 

Author Closing Comment

by:Majikthise
Comment Utility
Thank you!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now