How to join three tables in a query.

Question

Hello Dear Friends I am learning C# winapp and i want to create a query with three tables. I know how to join two tables with inner join but i got error while doing the same with three tables. here is my tables structure... 1.Table Employee EmpName Text EmpAdd Text EmpCityId Number EmpStatId Number 2.Table MasterCity CityId Number StateId Number City Text 3.Table MasterState StateId Number State Text I want to get a record from employee table. How can I join three tables that give me a full record of an employee with name address city and state? I am trying with > Select Employee.empname Employee.empadd Mastercity.city Masterstate.state from Employee inner join mastercity on Employee.empcityid= mastercity.cityid inner join employee.empstateid=masterstate.stateid Where Employee.EmpName='PINAKIN T SHAH' Please help out with how to correct the query? How to join three tables? Thank You Mayank Jani

Amit Gupta · Answer

Ah! As you haven't mentioned which database you are using I assumed of Sql Server and the query builder is for SQL. Unfortunately I haven't worked too much for MS Access as your query seems good just you need to add paratheses between your join. Have a look to the below link for usage https://stackoverflow.com/questions/19367565/access-sql-inner-join-with-multiple-tables

Puneet Kankar · Answer

you also try this type query for inner join select * from table1 a table2 b table3 c where a.id=b.id and b.id=c.id

Sudheshwer Rai · Answer

Select Employee.empname Employee.empadd Mastercity.city Masterstate.state from Employee inner join mastercity on Employee.empcityid=mastercity.cityid inner join MasterState ON Employee.empstateid=masterstate.stateid Where Employee.EmpName='PINAKIN T SHAH' Note - I think you have missed to mention third table name in your query. I have made changes please go through and let me know It's working or not. Hope it will help you.

Amit Gupta · Answer

Instead of writing query you can use Sql query builder by pressing CTRL +SHIFT+Q then you can add your 3 tables -> select the fields as required. Good luck

Mayank Jani · Answer

Hi Amit I just got it done!!! I have deleted all the things and recreated again. I purhaps missed one step at the time of crystal report wizard to link within three tables i am not sure about this. but the main things is the report is working now properly... the most surprising matter for me is I just tryed fetch all the values from just one table (i.e. Employee table only) to check what happnes next. then I thought that I will add the second table and then the third table to check the difference but when i ran the query with the first table i really surprised that i got all the result!!!!!! the query is Select * From Employee Where EmpName='Mayank' No two tables join no three tables join. that's it!!!!!!!! As far as i understood the problem was the links between the tables at the time of crystal report wizard. but I thank you so much for your efforts with patients. you have helped me a lot. a screen shot just to show the result. Thank You. Mayank Jani

Amit Gupta · Answer

Can you share your project with report for evaluation?

Mayank Jani · Answer

Hi Amit i only write query in button's click event. in crystal report wizard i connect database and datatables only then i fetch the fields in crstal report from field explorer. i have noticed that the query is working properly (both in Access and application) but the problem occures when i assign the values to crystal report and crystal report viewer. see the screen shot attached. Thanks Mayank Jani

Amit Gupta · Answer

Hey Mayank Have you checked your query in your Crystal Report I think there mght something wrong with this. SELECT Employee.EmployeeName Employee.EmpAdd MasterCity.City MasterState.State FROM (Employee INNER JOIN MasterCity ON Employee.EmpCityID = MasterCity.CityID) INNER JOIN MasterState ON MasterCity.StateID = MasterState.StateID where EmployeeName = 'amit'

Mayank Jani · Answer

Hi Everybody the answer is accepted and the query runs but there is a bug in the query or something else that does not show any error but also not the correct result. please see the attached zip file that contains screen shots of the application. you can easily understand that when every time the query runs it shows name and address correctly but the city and state remains the same. it fetches only the first row of both city and state tables and not as mentioned in employee table. it's surprises me that when i run the query in MS Access it works perfect!!! Please see the screen shots attached. Can anybody find the bug? Thank You. Mayank Jani.

issacpal · Answer

Hi Dude  Its very simpley try this: SELECT O.EmpName O.EmpAdd I.CityP.State FROM Employee O JOIN MasterCity I ON O.EmpCityId = I.CityId JOIN MasterState P ON P.StateId = I.StateId ORDER BY O.EmpName

Mayank Jani · Answer

Heyyyyyyyyyyyyy Amit thank you man thanks a lot. the link you gave worked. also parentheses was required in the query. anyway the query is working in Access but there is a little unwanted output in my crystal report but i will solve that. thank you very much once again. Mayank Jani

Mayank Jani · Answer

Hi Sudheshwar thanks for reply... yeah that was my mistake but still it shows an error...'Syntax error (missing operator) in query expression 'employee.empcityid=mastercity.cityid inner join masterstate on employee.empstateid=masterstate.stateid'. Mayank Jani

Mayank Jani · Answer

Hi Jashir thanks for reply... i got error -->'Syntex error in FROM clause.' this is my query... select employee.empname employee.empadd mastercity.city masterstate.state from employee join mastercity on employee.empcity=mastercity.cityid join employee on employee.empstate=masterstateid where employee.empname='ABC XYZ' I use MS Access database. 1.Table Employee EmpName Text >ABC XYZ EmpAdd Text >0 Somewhere here and there. EmpCityId Number >3 EmpStatId Number >2 2.Table MasterCity CityId Number >3 StateId Number >2 City Text >Ahmedabad 3.Table MasterState StateId Number >2 State Text >Gujarat Please let me know where i m mistaking. Please see table structure and record. Thank you Mayank Jani.

Mayank Jani · Answer

Hi Amit Nice to meet you again. :) what do you mean by sql query builder? I use Access database and i run query in Access to check whether it runs or not. other way i know is to create dataset (.xsd). Would you please show me the path to open what you said? Thank you Mayank Jani