what is mysql query (inner join query) for my following problem

I want to display information of employee as per selected category. (also calculate no employees of that category).

At a time only one category will be selected from dropdown list .on selecting category , the corresponding categoryid will be recieved.
On the basis of that categoryid  i want to count no of empl0yees and fetch list of employees belong to that selected category only.


Four categories are there
DRDS
DRTC
ADMIN
ALLIED
 there are three tables
Category master (category will be selected from here) [CategoryId]
designationmaster [DesignationId] [Category]
employee master [Designation]

CategoryId = Category
 DesignationId = Designation

CategoryId  of  category master should be matched with Category field of designationmaster.

DesignationId of designationmaster should be matched with Designation field of employeemaster


RESULT OF MY QUERY SHOULD BE NUMBER OF EMPLOYEES CALCULATED FOR SELECTED CATEGORY (category is selected through dropdown list and categoryid is received through post method in next submitted page


Screenshots of tables are attached.emp.PNGdesignation_master.PNGcategory_master.PNG
v sResearcherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mohan singhWeb developerCommented:
use inner join to get details from multiple table
but your  foreign key and  primary should be same with data type
mohan singhWeb developerCommented:
where is screen shot ?
v sResearcherAuthor Commented:
@mohan singh

files are attached now

yes my foreign keys and primary keys are of same data type
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

LEARN MORE
PortletPaulEE Topic AdvisorCommented: 
SELECT
      c.Category
    , COUNT (e.*) AS count_of
FROM employeemaster e
INNER JOIN designationmaster d ON e.designation = d.designationid
INNER JOIN Categorymaster c ON d.Category = c.Categoryid
GROUP BY
      c.Category

Open in new window

mohan singhWeb developerCommented:
hi @ V S

Mr PAUL give a example of inner join do as it is you will get what you want

Thank You

Mohan SIngh
v sResearcherAuthor Commented:
@mohan singh
 i am receiving categoryId through post method
where is that value should be mentioned in Mr paul's query...
PortletPaulEE Topic AdvisorCommented:
>>"I want to display information of employee category wise"

If category is NOT the right column to use, simply replace it with the correct column 

SELECT
      c.Category                   -- <<<<<<<<<<<<<<< here
    , COUNT (e.*) AS count_of
FROM employeemaster e
INNER JOIN designationmaster d ON e.designation = d.designationid
INNER JOIN Categorymaster c ON d.Category = c.Categoryid
GROUP BY
      c.Category                   -- <<<<<<<<<<<<<<< & here
;
===========================================================
-- like this

SELECT
      c.CategoryDesciption
    , COUNT (e.*) AS count_of
FROM employeemaster e
INNER JOIN designationmaster d ON e.designation = d.designationid
INNER JOIN Categorymaster c ON d.Category = c.Categoryid
GROUP BY
      c.CategoryDesciption
;

Open in new window

v sResearcherAuthor Commented:
@ Mr Paul

Sir at a time only one category will be selected from dropdown list .on selecting category , the corresponding categoryid will be received.
on the basis of that categoryid  i want to count no of empl0yees and fetch list of employees belong to that selected category only. not all categories$categoryid containing categoryid of selected category from dropdown list.
v sResearcherAuthor Commented:
can anyone pls help me with this
PortletPaulEE Topic AdvisorCommented:
SELECT
      c.CategoryDesciption
    , COUNT (e.*) AS count_of
FROM employeemaster e
INNER JOIN designationmaster d ON e.designation = d.designationid
INNER JOIN Categorymaster c ON d.Category = c.Categoryid
where c.Categoryid = $categoryid
GROUP BY
      c.CategoryDesciption
;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
v sResearcherAuthor Commented:
thank you @Mr Paul

it works !!
v sResearcherAuthor Commented:
@Mr paul

how it will be done if we want to count and display  number of employee in each category
PortletPaulEE Topic AdvisorCommented:
just remove the where clause, then the query will display each category and count as 2 columns for one row per categoty
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
joins

From novice to tech pro — start learning today.