rgb192
asked on
do not include original row, only the duplicate row
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)
)
want all the columns for duplicate "dept_name"
in the dept table. do not include original row, Only include the duplicate row.
the original row is the row that is entered first.
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)
)
want all the columns for duplicate "dept_name"
in the dept table. do not include original row, Only include the duplicate row.
the original row is the row that is entered first.
this will give you all dupliacte rows
select max(dept_id) , dept_name, num_employees
from dept
group by dept_name, num_employees
select max(dept_id) , dept_name, num_employees
from dept
group by dept_name, num_employees
ASKER
Unknown column 'd.dept_name' in 'on clause'
ASKER
select max(dept_id) , dept_name, num_employees
from dept
group by dept_name, num_employees
this doesnt have data from the users table
from dept
group by dept_name, num_employees
this doesnt have data from the users table
select dupl.*,first.dept_id from dept dupl
join (select dept_name , min(dept_id ) dept_id from dept group by dept_name) first
on dupl. dept_name =first.dept_name and dupl.dept_id >first.dept_id
try this.
select * from (
select *, ROW_NUMBER() over(partition by dept_name order by dept_name) iCount from dept)t
where iCount >1
select * from (
select *, ROW_NUMBER() over(partition by dept_name order by dept_name) iCount from dept)t
where iCount >1
select * from (
select *, ROW_NUMBER() over(partition by dept_name, num_employees order by dept_name, num_employees) iCount from dept)t
where iCount >1
select *, ROW_NUMBER() over(partition by dept_name, num_employees order by dept_name, num_employees) iCount from dept)t
where iCount >1
select u.* , d.* from
users inner join
(select max(dept_id) as dept_id , dept_name, num_employees
from dept
group by dept_name, num_employees ) d
on u.dept_name = d.dept_name
users inner join
(select max(dept_id) as dept_id , dept_name, num_employees
from dept
group by dept_name, num_employees ) d
on u.dept_name = d.dept_name
ASKER
Unknown column 'd.dept_name' in 'on clause'
select dupl.*,first.dept_id from dept dupl
join (select dept_name , min(dept_id ) dept_id from dept group by dept_name) first
on dupl. dept_name =first.dept_name and dupl.dept_id >first.dept_id
both these queries generate errors
select * from (
select *, ROW_NUMBER() over(partition by dept_name order by dept_name) iCount from dept)t
where iCount >1
select * from (
select *, ROW_NUMBER() over(partition by dept_name, num_employees order by dept_name, num_employees) iCount from dept)t
where iCount >1
closest to being right
but want num_employees(sum)
select u.* , d.* from
users u inner join
(select max(dept_id) as dept_id , dept_name, num_employees
from dept d
group by dept_name, num_employees ) d
on u.dept_name = d.dept_name
select dupl.*,first.dept_id from dept dupl
join (select dept_name , min(dept_id ) dept_id from dept group by dept_name) first
on dupl. dept_name =first.dept_name and dupl.dept_id >first.dept_id
both these queries generate errors
select * from (
select *, ROW_NUMBER() over(partition by dept_name order by dept_name) iCount from dept)t
where iCount >1
select * from (
select *, ROW_NUMBER() over(partition by dept_name, num_employees order by dept_name, num_employees) iCount from dept)t
where iCount >1
closest to being right
but want num_employees(sum)
select u.* , d.* from
users u inner join
(select max(dept_id) as dept_id , dept_name, num_employees
from dept d
group by dept_name, num_employees ) d
on u.dept_name = d.dept_name
there is no d.dept_name in this query. query is correct
If you need sum
select dupl. dept_name, sum(dupl.num_employees )
from dept dupl
join (select dept_name , min(dept_id ) dept_id from dept group by dept_name) first
on dupl. dept_name =first.dept_name and dupl.dept_id >first.dept_id
But you are really should cleanup your dept table, update dept_id and counts, and delete duplicate. Then use dept_id for joins and make sure that no duplicates are inserted - make deptName unique index for example
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
can you give the error details for the following query...
select * from (
select *, ROW_NUMBER() over(partition by dept_name, num_employees order by dept_name, num_employees) iCount from dept)t
where iCount >1
select * from (
select *, ROW_NUMBER() over(partition by dept_name, num_employees order by dept_name, num_employees) iCount from dept)t
where iCount >1
ASKER
Thanks
since you have identity column you can assume that one with smallest id is first one
select dupl.*,first.dept_id from dept dupl
join (select dept_name , min(dept_id ) dept_id from dept group by dept_name) first
on d. dept_name =first.dept_name and d.dept_id >first.dept_id