How to join three tables in a query.

Mayank Jani

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

14 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta9y ago
Accepted 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
+1
Mayank Jani9y ago
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
0
Amit Gupta9y ago
Can you share your project with report for evaluation?
0
Mayank Jani9y ago
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
0
Amit Gupta9y ago
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'
0
Mayank Jani9y ago
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.
0
issacpal9y ago
Hi Dude ,

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

0
Mayank Jani9y ago
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
0
Mayank Jani9y ago
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
0
Puneet Kankar9y ago
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
0
Sudheshwer Rai9y ago
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.
+1
Mayank Jani9y ago
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.
0
Mayank Jani9y ago
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
0
Amit Gupta9y ago
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
0