CODEWITHSUNDEEP
mysqlsql
kjones1876
kjones1876

Reputation: 762

SQL help on counting the rows of a LEFT JOIN on a condition ignoring the right side

I have a 2 tables, tickets a tokens. Tokens as a column user_type.

I'm trying to work out how to count all the tickets joined on tokens.ticket_id which don't have a tokens.user_type == 'ADMIN'. So user_type can equal anything, except admin.

The problem is that there may be 5 other tokens to a ticket.

I know a reasonable about of SQL knowledge but this one has had me stumped for a while.

Also sorry if the title wasn't written correctly. Thank you, Kieran!

edit

I may have explained it wrong. But that still returns more then it should. If it has a ADMIN token it shouldnt show up in the result, otherwise it should show up. And im adding this because stackoverflow was complaining about insubstantial changes to make an edit.

Upvotes: 1

Views: 100

Answers (4)

user359040
user359040

Reputation:

Try:

SELECT COUNT(DISTINCT Tickets.id) - 
       COUNT(DISTINCT CASE WHEN Tokens.user_type = 'ADMIN' THEN Tickets.id END)
FROM Tickets
LEFT JOIN Tokens ON Tokens.ticket_id = Tickets.id

If your summary doesn't require information from the Tokens table, this could be rewritten more efficiently as:

SELECT COUNT(*)
FROM Tickets
LEFT JOIN Tokens ON Tokens.ticket_id = Tickets.id AND Tokens.user_type = 'ADMIN'
WHERE Tokens.ticket_id IS NULL

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146557

Try:

Select Count(*) From Tickets t
Where Not Exists(Select * From tokens
                 Where ticket_Id = t.Ticket_id
                     And user_type ='Admin')

Upvotes: 1

djangojazz
djangojazz

Reputation: 13272

I would do an aggregate of a case when function, that way you could say edge case counts as well as totals:

declare @Person Table ( personID int identity, person varchar(8));

insert into @Person values ('Brett'),('Paul'),('John');

declare @Orders table ( OrderID int identity, PersonID int, Description varchar(32), Amount int);

insert into @Orders values (1, 'Shirt', 20),(1, 'Shirt', 50),(1, 'Shirt', 22),(1, 'Admin', 52),(2, 'Shirt', 22),(2, 'Admin', 52);

Select
    p.person
,   count(case when o.Description != 'Admin' then o.orderID end) as NonAdminOrders
,   count(o.orderID) as TotalOrders
from @Person p
    left join @Orders o on p.personID = o.PersonID
group by p.person

Upvotes: 0

Travesty3
Travesty3

Reputation: 14479

Something like this might be what you're looking for:

SELECT
    COUNT(DISTINCT Tickets.id)
FROM
    Tickets
    INNER JOIN Tokens
        ON Tokens.ticket_id = Tickets.id
        AND Tokens.user_type != 'ADMIN'

If you need to include tickets with no tokens, this might work:

SELECT
    COUNT(DISTINCT Tickets.id)
FROM
    Tickets
    LEFT JOIN Tokens
        ON Tokens.ticket_id = Tickets.id
WHERE
    Tokens.user_type != 'ADMIN'

Upvotes: 1

Related Questions