Because I received help from an earlier post, I was able to get a code that does correctly:

select *
from (
    select e.employeename, s.skilldescription,
        count(*) over(partition by e.employeeid) as count
    from employeeskills_t k
    inner join employee_t e on k.employeeid = e.employeeid
    inner join skill_t s on k.skillid=s.skillid 
) t
where count> 1
order by employeename, skilldescription

But it generates an extra column, count, which I don't need.

I don't understand SQL that well, so I dont know if a command exists.

CodePudding user response：

• since you have used windows function you will see the results of each row and so the column will remain there
• you have two option either use the group by , where you can just filter on the count values using having clause or you could use the windows function and then select only the relevant columns you need that would be:

with main as (
        select e.employeename, s.skilldescription,
            count(*) over(partition by e.employeeid) as count
        from employeeskills_t k
        inner join employee_t e on k.employeeid = e.employeeid
        inner join skill_t s on k.skillid=s.skillid 
       ) 
    select 
    e.employeename, 
    s.skilldescription,
    where count> 1
    order by employeename, skilldescription

version 2 :

   select e.employeename
        from employeeskills_t k
        inner join employee_t e on k.employeeid = e.employeeid
        inner join skill_t s on k.skillid=s.skillid 
group by 1
having count(e.employeenam) > 1

CodePudding user response：

If you are looking for distinct values of employeename, skilldescription with count of emp ids more than one, same can be achieved using group by & having. Please check if the below helps.

select e.employeename, s.skilldescription 
from employeeskills_t k
inner join employee_t e on k.employeeid = e.employeeid
inner join skill_t s on k.skillid=s.skillid 
group by e.employeename, s.skilldescription
having COUNT(e.employeeid) > 1
order by employeename, skilldescription;

CodePudding user response：

By default select * gives you all columns available in the from clause.

Here, the from clause it itself a subquery, which returns 3 columns ; the third column is the count, which is needed in the outer query for filtering (that's the "last" where clause).

Since we cannot not return that column from the subquery, we can instead ignore it in the output. This means we need to enumerate the columns we want, rather than blindly use *.

Columns in the outer query have the same names as those returned by the subuqery. Here there are only two columns that are needed, so that's quite short to write :

Just change that select * from (...) t where ...

... To : select employeename, skilldescription from (...) t where ...

Side note: some databases support a specific syntax to select all columns but a few named columns. BigQuery has SELECT * EXCEPT - but that's not a widely available feature in other RDBMS, unfortunately.