Help on multiple inner join query

2
votes

I have this query

SELECT t1.Trans_ID
FROM (SELECT * FROM Prune WHERE [Name]="I1")  AS t1 
INNER JOIN (SELECT * FROM Prune WHERE [Name]="I2")  AS t2 ON (t1.Trans_ID = t2.Trans_ID)
INNER JOIN (SELECT * FROM Prune WHERE [Name]="I3")  AS t3 ON (t1.Trans_ID = t3.Trans_ID)

and i got error msg : " Syntax error (missing operator) in query expression 

'(t1.Trans_ID = t2.Trans_ID)
INNER JOIN (SELECT * FROM Prune WHERE [Name]="I3")  AS t3 ON (t1.Trans_ID = t3.Trans_ID)'

But that query work perfectly when i'm using only 1 inner join 

SELECT t1.Trans_ID
FROM (SELECT * FROM Prune WHERE [Name]="I1")  AS t1 
INNER JOIN (SELECT * FROM Prune WHERE [Name]="I2")  AS t2 ON (t1.Trans_ID = t2.Trans_ID)

anyone can tell what happen? and how to solve it?
I'm working with ms access

2
sqlms-access

2 Answers

3
votes

MsAccess is sometimes full of shit...

Try this

SELECT t1.Trans_ID 
FROM ((SELECT * FROM Prune WHERE [Name]="I1")  AS t1  
INNER JOIN (SELECT * FROM Prune WHERE [Name]="I2")  AS t2 ON (t1.Trans_ID = t2.Trans_ID))
INNER JOIN (SELECT * FROM Prune WHERE [Name]="I3")  AS t3 ON (t1.Trans_ID = t3.Trans_ID)

Please note the additional open bracket just after FROM and additional closing bracket just after t2.Trans_ID)

2
votes

Try this:

SELECT t1.Trans_ID
FROM Prune AS t1
INNER JOIN Prune AS t2 ON t1.Trans_ID = t2.Trans_ID
INNER JOIN Prune AS t3 ON t1.Trans_ID = t3.Trans_ID
WHERE t1.Name = "I1" AND t2.Name = "I2" AND t3.Name = "I3"