Solved

tables are linked by dept_name

Posted on 2011-09-19
10
303 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
 
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
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.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 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

19 Experts available now in Live!

Get 1:1 Help Now