tables are linked by dept_name

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
LVL 1
rgb192Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Lara FConnect With a Mentor EACommented:
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
 
Lara FEACommented:
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
 
rgb192Author Commented:
if dept table has duplicate rows

full_name, dept_name,num_employees(sum)

only want one row for each full_name
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
ramkihardyCommented:
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
 
Lara FEACommented:
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
 
ramkihardyCommented:
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
 
rgb192Author Commented:
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
 
ramkihardyCommented:
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
 
rgb192Author Commented:
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
 
ramkihardyCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.