Find where there is only one for that ticket number

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Find where there is only one for that ticket number

Post by simonmlewis »

I need to run a MySQL query to find where there is only one of each ticket number per row, and where dateupdated IS NOT NULL.

I'm sure it' sa "having by" type query - anyone?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Find where there is only one for that ticket number

Post by requinix »

Not sure of the schema, but probably yes. Those queries tend to look like

Code: Select all

SELECT t1.unique_identifiers, ...
FROM table t1
JOIN table t2 ON ...
GROUP BY t1.unique_identifiers, ...
HAVING COUNT(1) = 1
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Find where there is only one for that ticket number

Post by simonmlewis »

Sorry I'm not really understanding that.
The table is called 'tickets'. There is the field 'ticket' where the there are the ticket numbers, in theory there is only one of them, but had got repliedto = yes and dateupdated IS NOT NULL.....

I'm trying to find where there is only one row for each "ticket", in tickets, where repliedto = YES and dateupdated IS NOT NULL.

Problem is, for some reason the repliedto field is getting populated when it should be, bu tno new row added - so only one row remains for that 'ticket' number, and I need to find each one that has got those fields with something in it.

I ask where it is HAVING 1, because that's the issue.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Find where there is only one for that ticket number

Post by requinix »

So you want to find each ticket number that has only one "tickets" entry, and that entry has repliedto=yes and dateupdated not null? Assuming you have a unique field (eg, primary key) named "id" then I think I would go with a JOIN.

Code: Select all

SELECT t1.ticket
FROM tickets AS t1
LEFT JOIN tickets AS t2 ON t1.ticket = t2.ticket AND t1.id != t2.id
WHERE t1.repliedto = 'yes' AND t1.dateupdated IS NOT NULL AND t2.id IS NULL
The reason is that a GROUP BY/HAVING makes it harder to filter out the ticket rows not matching the repliedto/dateupdated criteria. With a JOIN (a) you can put whatever criteria you want in there and (b) it's easy to find rows that have no "siblings" (ie, other rows for the same ticket number).
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Find where there is only one for that ticket number

Post by simonmlewis »

Sorry to sound dumb here .... but how does that work?
It's found actually only the last one remaining one I couldn't find. But running locally wiht an older set of values, it found 21. (I found 20 manually earlier on the current system).
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Find where there is only one for that ticket number

Post by requinix »

Basically,
1. Start with the (first) tickets table. Call it "t1".
2. For each row in t1, join in the (second) tickets table, calling that "t2". A row from t2 can be matched with the row from t1 based on those two conditions: same ticket number but not the same row.
3. Because it's an outer join (the "LEFT"), if MySQL doesn't find any matching rows in t2 then it will use a "fake" row of entirely NULLs.
4. Filter the results down to the t1+t2 rows where t1 matches the two WHERE criteria and the id from t2 is NULL.
5. Because t2.id is non-nullable (it's unique/the primary key) the only possible way it could be NULL is if we got one of those "fake" rows during the JOIN.

Look at it with a smaller data set.

Code: Select all

id | ticket | match
---+--------+------
1  |    123 |   yes
2  |    234 |    no
3  |    345 |   yes
4  |    345 |    no
The end result should be just [123] since 234 doesn't match and 345 has two rows.

Code: Select all

SELECT *
FROM tickets AS t1
LEFT JOIN tickets AS t2 ON t1.ticket = t2.ticket;

id | ticket | match | id | ticket | match
---+--------+-------+----+--------+------
1  |    123 |   yes |  1 |    123 |   yes
2  |    234 |    no |  2 |    234 |    no
3  |    345 |   yes |  3 |    345 |   yes
3  |    345 |   yes |  4 |    345 |    no
4  |    345 |    no |  3 |    345 |   yes
4  |    345 |    no |  4 |    345 |    no

Code: Select all

SELECT *
FROM tickets AS t1
LEFT JOIN tickets AS t2 ON t1.ticket = t2.ticket AND t1.id != t2.id;

id | ticket | match | id | ticket | match
---+--------+-------+----+--------+------
1  |    123 |   yes |  N |   NULL |  NULL
2  |    234 |    no |  N |   NULL |  NULL
3  |    345 |   yes |  4 |    345 |    no
4  |    345 |    no |  3 |    345 |   yes

Code: Select all

SELECT *
FROM tickets AS t1
LEFT JOIN tickets AS t2 ON t1.ticket = t2.ticket AND t1.id != t2.id
WHERE t1.match = 'yes';

id | ticket | match | id | ticket | match
---+--------+-------+----+--------+------
1  |    123 |   yes |  N |   NULL |  NULL
3  |    345 |   yes |  4 |    345 |    no

Code: Select all

SELECT *
FROM tickets AS t1
LEFT JOIN tickets AS t2 ON t1.ticket = t2.ticket AND t1.id != t2.id
WHERE t1.match = 'yes' AND t2.id IS NULL;

id | ticket | match | id | ticket | match
---+--------+-------+----+--------+------
1  |    123 |   yes |  N |   NULL |  NULL
Post Reply