Solved

linked by dept_name

Posted on 2011-09-18
4
410 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 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

695 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