Solved

linked by dept_name

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now