There is a staff table and each staff can have more than one card (in different types). But the staff can use a single card on the same day.

In the example, personnel used both cards on the same day or gave their card to someone else.

How can I write a query that can find this in Oracle? Thanks.

Data

CodePudding user response：

You can:

• apply a JOIN operation to match the two tables,
• aggregate on same ("PersonelId", "Date") pairs,
• filter out "PersonelId" values having only one appearance in every day
• get these values distinctively (if the same person has used different own cards in different days, that person would appear more than once)

SELECT DISTINCT c.PersonelId
FROM       entrances e
INNER JOIN cards c
        ON e.CardId = c.CardId
GROUP BY c.PersonelId, Date_
HAVING COUNT(c.PersonelId) > 1

Check the demo here.

CodePudding user response：

You can use COUNT(DISTINCT CardId) for each personnel and date, and check if more than one cardid was used:

select c.PersonelId
    , e.Date_
from #cards c
     inner join
     #entrances e
     on c.CardId=e.CardId
group by c.PersonelId, e.Date_
having count(distinct e.CardId) > 1