?
Solved

linked by dept_name

Posted on 2011-09-18
4
Medium Priority
?
416 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 93

Accepted Solution

by:
Patrick Matthews earned 668 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 664 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 668 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

839 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