v s
asked on
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
mohan singh
where is screen shot ?
ASKER
@mohan singh
files are attached now
yes my foreign keys and primary keys are of same data type
files are attached now
yes my foreign keys and primary keys are of same data type
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@mohan singh
i am receiving categoryId through post method
where is that value should be mentioned in Mr paul's query...
i am receiving categoryId through post method
where is that value should be mentioned in Mr paul's query...
>>"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
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
;ASKER
@ 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
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
ASKER
can anyone pls help me with this
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you @Mr Paul
it works !!
it works !!
ASKER
@Mr paul
how it will be done if we want to count and display number of employee in each category
how it will be done if we want to count and display number of employee in each category
just remove the where clause, then the query will display each category and count as 2 columns for one row per categoty