0

i doing some project using Java(netbeans sw) and link to Microsoft Access.

The problem occur when i need to inner join 3 tables together from Microsoft Access,

i have no problem to inner join 2 tables together 

rsUpdate = 

stmtUpdate.executeQuery("SELECT * FROM A_User Inner Join A_PC ON A_USER.SN = A_PC.SN");

which i able to get the result. But not inner join with 3 tables

rsUpdate = 

stmtUpdate.executeQuery

("SELECT * FROM A_User Inner Join A_CPU ON A_USER.SN = A_CPU.SN , Inner Join A_Software ON A_CPU.SN = A_Software.SN")

For the SQL above I have 3 "A" table separately for USER | CPU | Software|

USER PK is SN | CPU FK is SN | Software PK is SN |

The Error I got java.sql.SQLException:Characters found after end SQL statement

Thanks

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
FookShen
  • 9
  • 1
  • 1
  • 5
  • 1
    Possible duplicate [SQL Inner-join with 3 tables?](http://stackoverflow.com/questions/10195451/sql-inner-join-with-3-tables) – Baby Dec 31 '13 at 07:33
  • 1
    You don't need comma "," between two `INNER JOIN` statement. `SELECT * FROM A_User Inner Join A_CPU ON A_USER.SN = A_CPU.SN Inner Join A_Software ON A_CPU.SN = A_Software.SN` – Tun Zarni Kyaw Dec 31 '13 at 07:35
  • i tried without comma but it prompt out syntax error in query expression – FookShen Dec 31 '13 at 08:05
  • @RafaEl u r suggesting i use 3 table with same primary keys ? – FookShen Dec 31 '13 at 08:09

4 Answers4

3
rsUpdate = 

stmtUpdate.executeQuery

("SELECT * FROM A_User
Inner Join A_CPU ON A_USER.SN = A_CPU.SN
Inner Join A_Software ON A_CPU.SN = A_Software.SN");

no need for ',' here... try this above code

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
DINESH4DINA
  • 45
  • 6
2

For Ms Access, when you JOIN more than table, the syntax is different. It should be this way:

SELECT * 
FROM   ((a_user 
         INNER JOIN a_cpu 
                 ON a_user.sn = a_cpu.sn) 
        INNER JOIN a_software 
                ON a_cpu.sn = a_software.sn)
Hawk
  • 5,060
  • 12
  • 49
  • 74
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • so you means (SELECT * FROM (A_USER Inner Join A_CPU on A_User.SN = A_CPU.SN) Inner Join A_Software ON A_CPU.SN = A_Software.SN) ? – FookShen Dec 31 '13 at 08:05
  • is that your typo Inner INNER join ? – FookShen Dec 31 '13 at 08:07
  • @FookShen - Sorry see my edit. Ms Access has a different syntax when joining multiple tables. This should work fine. Please try it. – Mahmoud Gamal Dec 31 '13 at 08:13
  • if i use Innner INNER JOIN it will prompt out Syntax error in JOIN Operation. – FookShen Dec 31 '13 at 08:32
  • @FookShen So sooory, it is a typo indeed, didn't so that sorry, remove it. – Mahmoud Gamal Dec 31 '13 at 08:37
  • but if i use Select * FROM(A_User Inner Join A_CPU on A_User.SN = A_CPU.SN) Inner Join A_Software ON A_CPU.SN = A_Software.SN) it will pop out "error in row", which is means the following code might correct just the input field to link Access is not organize yet – FookShen Dec 31 '13 at 08:38
  • @FookShen - Try it first in the Access directly and ensure that it is working fine. It should work fine – Mahmoud Gamal Dec 31 '13 at 08:41
  • @MahmoudGamal Sure, keep it up ;) – Hawk Dec 31 '13 at 09:37
1

There should be no comma after the first join

rsUpdate = 

stmtUpdate.executeQuery

("SELECT * FROM A_User Inner Join A_CPU ON A_USER.SN = A_CPU.SN  Inner Join A_Software ON A_CPU.SN = A_Software.SN")
stinepike
  • 54,068
  • 14
  • 92
  • 112
0

Problem Solved

For example -

Table A | Username(PK)| Address|

Table B | ID | Phone | Username(FK)|

Table C | SN | Brand | Model | Username(FK) 

rs = st.executeQuery

("SELECT * FROM (A Inner Join B on A.Username = B.Username) Inner Join C on A.Username = C.Username");

if anyone looking for inner join 3 tables together by Using JAVA and Link to Access use the referenece above.

Make sure you must link the table relationship in Access before run the java program if not it will pop out "ERROR IN ROW"

Thanks everyone who helping me :)

FookShen
  • 9
  • 1
  • 1
  • 5