Problem with joining tables in MSAccess.

[A k ch]

I am using MS Access data base here i have a problem with Joining of the tables which giving me a error like operator syntax error. i came to know that we have to use paranthesis to join tables in MS Access, but i don't know how to use them. Here i am posting my SQL query can any body give me solution for this. This is query is working fine in SQL server but i want it in MS Access. select * from students a left join subdetails b on a.bid=b.bid left join subjects c on b.subid=c.subid left join details d on c.subid=d.subid and a.hno=d.hno where d.hno=1234 and b.bid=2 and b.semid=1 Please help me.

Thanks & Regards, Anil Chelasani

[riced]

Use query designer to construct the query then switch to SQL view. You can then copy and paste resulting SQL. Note that Access tends to put in redundant brackets but they don't cause any problem.

Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis

[A k ch]

Thanks.

Thanks & Regards, Anil Chelasani

[Niladri_Biswas]

Try this

SELECT * FROM
(

                             ( STUDENTS AS A  LEFT JOIN SUBDETAILS AS B ON A.BID=B.BID)
                               
                              LEFT JOIN SUBJECTS C ON B.SUBID=C.SUBID
                        )

                       LEFT JOIN DETAILS D ON C.SUBID=D.SUBID 

               WHERE D.HNO=1234 

               AND B.BID=2 

               AND B.SEMID=1

               AND A.HNO=D.HNO

N.B.~ If you want to perform more than one left join in MS Access, you should use parenthesis in the From clause. Hope this helps :)

Niladri Biswas

[A k ch]

Thanks this is working fine but when i am trying to check second condition in the ON that is like LEFT JOIN DETAILS D ON C.SUBID=D.SUBID AND C.HNO = A.HNO it is again giving the error. can you please tell me how to check for second condition in the "ON".

Thanks & Regards, Anil Chelasani

[riced]

Here's the SQL generated by the query designer. Two versions: first with fixed values, second using parameters. Note the redundant parentheses (esp in WHERE) - and the layout is pretty poor.

SELECT a.*
FROM ((Student AS a LEFT JOIN Subdetails AS b ON a.bid = b.bid)
LEFT JOIN Subjects AS c ON b.subid = c.subid)
LEFT JOIN details AS d ON c.subid = d.subid
WHERE (((b.bid)=2) AND ((b.semid)=1) AND ((d.hno)=1234) AND ((d.hno)=[a].[hno]));

PARAMETERS inBid Long, inSemid Long, inHno Long;
SELECT a.*
FROM ((Student AS a LEFT JOIN Subdetails AS b ON a.bid = b.bid)
LEFT JOIN Subjects AS c ON b.subid = c.subid)
LEFT JOIN details AS d ON c.subid = d.subid
WHERE (((b.bid)=[inBid]) AND ((b.semid)=[inSemid]) AND ((d.hno)=[inHno]) AND ((d.hno)=[a].[hno]));

Looking at your original post I suspect this is a problems left join details d on c.subid=d.subid and a.hno=d.hno The tables on both sides of the and must be the same i.e. c=d or a=d - cannot have c=d and a=d in same join clause.

Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis

[A k ch]

it worked fine. Thanks for ur time and consideration.

Thanks & Regards, Anil Chelasani