?
Solved

linked by dept_name

Posted on 2011-09-18
4
Medium Priority
?
412 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.

764 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