Solved

tables are linked by dept_name

Posted on 2011-09-19
10
307 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Where clause in stored procedure 8 64
SQL Agent Timeout 5 69
Upgrading SQL 2005 Express to 2008 R2 Express 31 155
Grid querry results 41 82
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

735 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