Solved

linked by dept_name

Posted on 2011-09-18
4
403 Views
Last Modified: 2012-08-14
 CREATE TABLE users (
    user_id int(20) AUTO_INCREMENT NOT NULL,
    full_name varchar(50),
    telephone varchar(15),
    dept_id int(20),
    dept_name varchar(50),  
    users.dept_id
   PRIMARY KEY (user_id)
  )
 

  CREATE TABLE dept (
    dept_id INT(11) AUTO_INCREMENT NOT NULL,  
    dept_name VARCHAR(50),
    num_employees INT(11),
   PRIMARY KEY (dept_id)
  )

tables are linked by dept_name
want full_name, dept_name, num_employees
0
Comment
Question by:rgb192
4 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 167 total points
ID: 36558049
>>tables are linked by dept_name

Why on earth would you do that? dept_id is the best foreign key.  Indeed, having dept_name in the users table is a really bad idea.

I recommend:

1) Remove dept_name from users

2) Forget that num_employees column.  Calculate it on demand instead

3) Query:

SELECT u.full_name, d.dept_name, COUNT(u.user_id) AS num_employees
FROM users u INNER JOIN
    dept d ON u.dept_id = d.dept_id
GROUP BY u.full_name, d.dept_name
0
 
LVL 9

Assisted Solution

by:sachinpatil10d
sachinpatil10d earned 166 total points
ID: 36558199
what matthewspatrick says is perfect.
even though you don't want to change your table structure then use can use this
but join in your current table structure in not good as it will slow down the query process.

SELECT u.full_name, d.dept_name, COUNT(u.user_id) AS num_employees
FROM users u INNER JOIN
    dept d ON u.dept_name= d.dept_name
GROUP BY u.full_name, d.dept_name
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 167 total points
ID: 36558365
CREATE TABLE users (
    user_id int(20) AUTO_INCREMENT NOT NULL,
    full_name varchar(50),
    telephone varchar(15),
    dept_id int(20), FK (Dept.dept_id)
   PRIMARY KEY (user_id)
  )
 

  CREATE TABLE dept (
    dept_id INT(11) AUTO_INCREMENT NOT NULL,  
    dept_name VARCHAR(50),
    num_employees INT(11),
   PRIMARY KEY (dept_id)
  )


Select full_name, dept_name, count(1) From
Users left outer join dept
on users.dept_id = dept.dept_id
group by full_name, dept_name
0
 

Author Closing Comment

by:rgb192
ID: 36563730
thanks
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question