Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello,

I have 2 table :
Employees
C#
ID Name CityID
1  asd  1


City
C#
ID Name
1  Mumbai
2  Pune


I want Employees belongings to more than 3 cities.But Employees assinging to same City should be count as 1.
For Example
Employee Table Data
C#
ID Name CityID
1  asd  1
2  asd  1


this Count will ne 1.

Thank You
Posted
Updated 14-Jun-15 23:25pm
v3
Comments
[no name] 15-Jun-15 5:51am    
I have tried with group. I get the output.but the problem is dat when same city comes it increses the count
For Example : der is Employee with duplicate records having Same cities 4 times
so count should be only 1.
Peter Leow 15-Jun-15 5:47am    
How come employee named 'asd' has 2 ids in the employee table? Re-look into the database design.
[no name] 15-Jun-15 5:49am    
Can u help me with table design.i want two table one city and Other employee.I want that single employee can be assigned to one or more cities.
Peter Leow 15-Jun-15 6:02am    
See solution 2.

First, thank you for specifying that this is homework. We are limited in how much help we can give for homework, but this is for your benefit. I have know people to fail due to tutors calling direct help from these sites 'plagiarism'. Also, our approach is better for your education if you let us know :)

So: two issues. The second is easy once you have dealt with the first: The table has duplicates.

Duplicates are going to make your counts inaccurate. Any aggregate you attempt to use will be wrong. What you need is a clean table to group.

You need to create this 'clean' table in a query and then run the count query. Here you have several options:

Common Table Expressions:
My personal fav. They can be tricky to get your head around but are really efficient!
There are plenty of resources on this subject. Here's an article to get you started:
Common Table Expressions(CTE) in SQL SERVER 2008[^]

Inline table query:
This is probably the easiest to implement. It just means that you define your table as a select before you use it. It might look something like this:

SQL
select count(*), groupid
from
    (select distinct id, groupid from mytable)
group by groupid



Finally, you can create a temporary table to store your distinct data before you select from it. There are several ways of defining temporary tables:
create table #table(id int, groupid int) will be created in the tempdb
delcare @table table (id int, groupid int) will be created in memory only
etc...

This technique is very heavy but is sometimes the easiest to understand.


Look into each and pick your fav

Hope that helps ^_^
 
Share this answer
 
1. First, the city table is fine.
2. As for the employee table, it should just stores only employee particulars, cityID has no place here.
3. Both the city and employee tables have a many-to-many relationship. Think of it as "each employee can be assigned to more than one city, and each city can be assigned to more than one employee." Got it?
4. How to link them? Not directly. You need a third table, let call it emp_city table with at least 2 fields:
emp_id and city_id, both form the composite primary key and each points to the respective primary keys in the employee and city tables. In other words, they are foreign keys to employee and city table respectively.
employee table        emp_city table           city table
id----------------------emp_id                   cityName
name                    city_id-------------------cityId

Learn more:
1. http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html[^]
2. http://www.studytonight.com/dbms/database-normalization.php[^]
Get the design right first.
 
Share this answer
 
v3
Comments
[no name] 15-Jun-15 7:26am    
Thank u so Much Peter Leow

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900