How do you find entry in DB where there is only one result?

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:

How do you find entry in DB where there is only one result?

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do you find entry in DB where there is only one resu

Post 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?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you find entry in DB where there is only one resu

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do you find entry in DB where there is only one resu

Post by Celauran »

Depends on the field type, but you could use IS NULL or = '' or even = 0
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you find entry in DB where there is only one resu

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do you find entry in DB where there is only one resu

Post 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?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you find entry in DB where there is only one resu

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do you find entry in DB where there is only one resu

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you find entry in DB where there is only one resu

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply