Page 1 of 1
How do you find entry in DB where there is only one result?
Posted: Thu May 22, 2014 5:22 am
by simonmlewis
I need to show a figure for how many unanswered messages there are.
Each message is given a ticket number. And when it's been replied to, it get a "dateupdated" date in it. However, Each reply that comes in, doesn't have that date yet, so appears "unanswered". Those messages are "ongoing".
I need to spot those that are completely unanswered.
I did this about adding a field "firstquestion". While that field remains null, it is unanswered. Once it is answered the "dateupdated" field is populated, and so is that.
However, is there way to say "find all entries where there is only one version of "ticket", and dateupdated is NULL?
Re: How do you find entry in DB where there is only one resu
Posted: Thu May 22, 2014 7:08 am
by Celauran
simonmlewis wrote:However, is there way to say "find all entries where there is only one version of "ticket", and dateupdated is NULL?
As it currently stands, wouldn't you want to search for all rows where firstquestion is null? Perhaps more importantly,
Each reply that comes in, doesn't have that date yet, so appears "unanswered".
Why?
Do tickets and their replies have a parent/child relationship whereby each reply belongs to a ticket? Mightn't it be simpler to look for tickets with no child entities?
Re: How do you find entry in DB where there is only one resu
Posted: Thu May 22, 2014 7:13 am
by simonmlewis
As it currently stands, wouldn't you want to search for all rows where firstquestion is null? Perhaps more importantly,
Yes - how?
As each one is replied to, the dateupdated field gets updated. So this would resolve it.
Re: How do you find entry in DB where there is only one resu
Posted: Thu May 22, 2014 7:18 am
by Celauran
Depends on the field type, but you could use IS NULL or = '' or even = 0
Re: How do you find entry in DB where there is only one resu
Posted: Thu May 22, 2014 7:23 am
by simonmlewis
I don't think you quite understand me.
I want to find the first one in each instance, where dateupdated IS NULL.
The "first one" only.
Re: How do you find entry in DB where there is only one resu
Posted: Thu May 22, 2014 7:27 am
by Celauran
Ticket #123 - no replies - dateupdated = null. Find this one.
Ticket #234 - two replies - dateupdated = 2014-05-20
Reply to ticket #234 - dateupdated = null. Don't return this.
Another reply to ticket #234 - dateupdated = null. Don't return this.
Is that not what you're after?
Re: How do you find entry in DB where there is only one resu
Posted: Thu May 22, 2014 7:31 am
by simonmlewis
That's exactly it. but my script keeps finding that 2nd and 3rd entry for #234. How do I make it find only the FIRST entry? Thus, not showing it in the result because "dateupdated" is not null.
Re: How do you find entry in DB where there is only one resu
Posted: Thu May 22, 2014 7:36 am
by Celauran
WHERE dateupdated IS NULL is an insufficient condition. What distinguishes a reply from a 'parent'? Add that to your WHERE clause.
Code: Select all
SELECT foo, bar
FROM tickets
WHERE in_reply_to IS NULL
AND dateupdated IS NULL
or some such.
Re: How do you find entry in DB where there is only one resu
Posted: Thu May 22, 2014 8:02 am
by simonmlewis
So you cannot ask it to find only the FIRST row, per 'ticket' number?
I mean I could just create a field called "openingquestion", and once that is replied to, it's updated, and then query on dateupdated and "openingquestion".
But I wondered if you could just query on the first row of each ticket number.