?
Solved

SQL joining

Posted on 2005-05-15
6
Medium Priority
?
195 Views
Last Modified: 2010-04-07
Hi,

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?


ayha
0
Comment
Question by:ayha1999
  • 3
  • 2
6 Comments
 
LVL 23

Expert Comment

by:b1xml2
ID: 14005448
SELECT
      *
FROM
      [tblOut]
      INNER JOIN [tblProf] ON [tblProf].[ID] = [tblOut].[Picked]
      INNER JOIN [tblIN] ON [tblIN].[CardNo] = [tblOut].[CardNo]
0
 
LVL 7

Author Comment

by:ayha1999
ID: 14009127
Hi,

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

tblProf
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.

tblProf
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.

ayha
0
 
LVL 4

Expert Comment

by:Koala119
ID: 14009641
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:ayha1999
ID: 14019911
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.

ayha
0
 
LVL 4

Accepted Solution

by:
Koala119 earned 1000 total points
ID: 14023274
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
0
 
LVL 4

Expert Comment

by:Koala119
ID: 14023360
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
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…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month15 days, 21 hours left to enroll

850 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