SQL joining

Posted on 2005-05-15
Last Modified: 2010-04-07

the structures of the Access2000 tables as follows.

tblIN     tblOut    tblProf
CardNo    CardNo    ID
...       Picked    Name

I wanto select all records from the two tables for the following criteria.

lblIN.CardNo = tblOut.CardNo
tblOut.Picked= tblProf.ID

can anyone pls. help me with sql statements?

Question by:ayha1999
    LVL 23

    Expert Comment

          INNER JOIN [tblProf] ON [tblProf].[ID] = [tblOut].[Picked]
          INNER JOIN [tblIN] ON [tblIN].[CardNo] = [tblOut].[CardNo]
    LVL 7

    Author Comment


    None of the answers worked. I think my question was not clear.
    I will explain:
    Suppose I have the following data in tblProf table;

    ID    Name
    1     ABC

    Pick are related to ID field of Prof.
    I want to retrive all records from tblIn and tbnOut and ...

    suppose Pick=1 then I want retrieve Names from Prof table for ID equal to 1.

    ID  Name
    1   Abc

    I don't want to use WHERE in query because sometimes my query will look like:

    select * from tblIn,tblOut,tblProf Where CardNo = ' txtCardNo ' Or
    select * from tblIn,tblOut,tblProf Where Pick= ' txtPick 'etc.

    LVL 4

    Expert Comment

    If you want to get all the tblProf.Name, tblIn.*, tblOut.*, then try this:

    SELECT tblProf.Name, tblIn.*, tblOut.*
    FROM tblProf INNER JOIN tblOut ON tblProf.ID = tblOut.Picked
             INNER JOIN tblIN ON lblIN.CardNo = tblOut.CardNo

    If you want to get a particular record, you have to add Where clause.
    LVL 7

    Author Comment

    Hi Koala119,

    I tried ur query but I get the error that...
    Syntax error, missing operator then query...

    SELECT TblProf.Name, TblIn.*, TblOut.* FROM TblProf INNER JOIN TblOut ON TblProf.ID = TblOut.Pick INNER JOIN TblIn ON TblIn.CardNo = TblOut.CardNo

    could u pls. check what's wrong?

    thanks in advance.

    LVL 4

    Accepted Solution

    I don't think there is any syntax error in this query.
    Are you running the query in MS Access directly? There should not have any problem if you run it here, you can test it.
    Or running as part of the codes  in vb .net? If yes, could you post your codes? I guess the problem might be here.

    You may try this as well:
    SELECT TblProf.Name, TblIn.*, TblOut.*
    FROM TblProf, TblOut, TblIn
    WHERE TblProf.ID = TblOut.Pick AND TblIn.CardNo = TblOut.CardNo
    LVL 4

    Expert Comment

    One thing about the error, it may be because of the datatype different. Could you check if they have the same datatype in MS Access e.g.Number, Text
         1. TblProf.ID = TblOut.Pick
         2. TblIn.CardNo = TblOut.CardNo

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    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.

    This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
    A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    758 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

    10 Experts available now in Live!

    Get 1:1 Help Now