Solved

tables are linked by dept_name

Posted on 2011-09-19
10
305 Views
Last Modified: 2012-05-12
tables are linked by dept_name

 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
  • 3
  • 3
10 Comments
 
LVL 11

Expert Comment

by:Larissa T
ID: 36564790
Both table have dept_id  Are you sure they are linked by deptName?

select users.full_name, users.dept_name, dept.num_employees
from users  join dept  on users.dept_name = dept .dept_name

Or this relates to your other question and you do not rely on dept_id being unique for departmentName

BTW - you can update dept_id in table user to first dept_id created  in table dept

0
 

Author Comment

by:rgb192
ID: 36564809
if dept table has duplicate rows

full_name, dept_name,num_employees(sum)

only want one row for each full_name
0
 
LVL 2

Expert Comment

by:ramkihardy
ID: 36564813
select a.full_name,a.dept_name,b.num_employees from users a join  dept b on a.deptname=b.deptname

by using this query you will get the desired result..

But you not supposed to use name column as a foreign key column use the ID column that is highly recommended..
Regrds
Ramki
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Expert Comment

by:Larissa T
ID: 36564829
You need first cleanup duplicate or use query from other question as

select users.full_name, users.dept_name, dept.num_employees
from users
join (select dept_name , min(dept_id ) dept_id  from dept  group by dept_name)  as first
on users. dept_id =first.dept_id
join dept  on first.dept_id = dept .dept_id
0
 
LVL 2

Expert Comment

by:ramkihardy
ID: 36564833
select distinct a.full_name,a.dept_name,b.num_employees from users a join  dept b on a.deptname=b.deptname
use this..
0
 

Author Comment

by:rgb192
ID: 36564845
select distinct a.full_name,a.dept_name,b.num_employees from users a join  dept b on a.deptname=b.deptname

select users.full_name, users.dept_name, dept.num_employees
from users
join (select dept_name , min(dept_id ) dept_id  from dept  group by dept_name)  as first
on users. dept_id =first.dept_id
join dept  on first.dept_id = dept .dept_id

correct: returning one row for each name
incorrect: needs to return sum of num_employees
0
 
LVL 2

Expert Comment

by:ramkihardy
ID: 36564854
select distinct a.full_name,a.dept_name,b.sum(num_employees) from users a join  dept b on a.deptname=b.deptname

It will return the sum of the number of employees...
Regards
Ramki
0
 
LVL 11

Accepted Solution

by:
Larissa T earned 500 total points
ID: 36564864
select users.full_name, users.dept_name, sum(dept.num_employees)
from users  join dept  on users.dept_name = dept .dept_name
group by users.full_name, users.dept_name
0
 

Author Closing Comment

by:rgb192
ID: 36564885
FUNCTION b.sum does not exist
select distinct a.full_name,a.dept_name,b.sum(num_employees) from users a join  dept b on a.deptname=b.deptname


works
select users.full_name, users.dept_name, sum(dept.num_employees)
from users  join dept  on users.dept_name = dept .dept_name
group by users.full_name, users.dept_name
0
 
LVL 2

Expert Comment

by:ramkihardy
ID: 36564890
select distinct a.full_name,a.dept_name,sum(b.num_employees) from users a join  dept b on a.deptname=b.deptname

actually its a typing mistake....this is the correct query
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
configure service broker on all databases 2 83
CREATE DATABASE ENCRYPTION KEY 1 67
Unable to save view in SSMS 21 70
kill process lock Sql server 9 54
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

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