Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

tables are linked by dept_name

Posted on 2011-09-19
10
Medium Priority
?
317 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
[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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

610 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