Basic Query, GROUP BY not working - finds those I don't want

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:

Basic Query, GROUP BY not working - finds those I don't want

Post by simonmlewis »

Code: Select all

$result = mysql_query ("SELECT * FROM chat WHERE status = 'closed' AND website = '$cookiewebsite' AND user != 'Operator' GROUP BY session ORDER BY id DESC");
This DB table has lots of rows of data. One row might have a user as 'user' and one might have a user as 'Operator', but each "set" has the same session ID number.

So you may have a load of rows with one Session ID, and many of those have 'Operator' as a user to has entered into that row.

But I want to find a chunk where they are all the same Session ID, but NO user entries for that Session ID say 'Operator'.

My script above should do it, but it's finding those where the user is 'user' but not Operator, even tho there are rows with the same Session ID that DO have Operator, and so it produces results that I don't want.

I need it to find chunks of Session ID, that do NOT have "operator" as a user entry.
Last edited by simonmlewis on Fri Aug 12, 2011 8:14 am, edited 1 time in total.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Re: Basic Query, where no row contains a certain word - how?

Post by Pyrite »

Just don't select *, and either use everything you select in the GROUP BY clause or use an aggregate function (like COUNT() perhaps).
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Basic Query, where no row contains a certain word - how?

Post by simonmlewis »

Sorry I don't quite understand.
Whether I select * or select only the 5 fields I need (which is basically ALL), the result is the same problem.
And I can't be a COUNT() because I need to extract the actual database, not just the amount of results.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Basic Query, where no row contains a certain word - how?

Post by simonmlewis »

Perhaps I haven't explained things too well.

5 rows in the table.
Each row has a session ID number of 111 (for ex.).
Rows 3 and 4 have 'Operator' in their 'User' field.

Therefore, this chunk of 5 rows must NOT be shown in the results.

2 rows in the tabls.
Each row has a session ID number 151 (for ex.).
Neither rows have 'Operator' in their 'User' fiend.

Therefore this MUST be shown in the results because NEITHER fields have been used by the 'Operator'.

In my code so far, it is results BOTH these examples. It only shows the 'User' entry in the 'GROUP BY' result shown, but when you view all records of those 5 rows, it shows that there are rows in there for 'Operator'.

THIS IS AN INCORRECT RESULT.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Basic Query, where no row contains a certain word - how?

Post by simonmlewis »

All the best to you, but may I please ask how this helps with this thread?
It reads like a simple query, but nothing seems to work.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Basic Query, where no row contains a certain word - how?

Post by simonmlewis »

Another way to put it:
Select ALL from chats WHERE none of the 'user' fields say 'Operator' in the GROUP BY of session '123'.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Basic Query, GROUP BY not working - finds those I don't

Post by VladSun »

1. What Pyrite means is that ALL non-aggregate columns in the SELECT clause should present in the GROUP BY clause.

2. While it seems to be a simple query ... it's not so simple :)

Something like this should work:

Code: Select all

SELECT
	`session`,
	MAX( IF(`user` = 'Operator', 1, 0) ) as `hasOperator`
from
	`table`
group BY
	`session`
having 
	`hasOperator` = 0
WHERE clause is applied before grouping, HAVING is a "WHERE" clause applied after grouping.
Last edited by VladSun on Mon Aug 15, 2011 5:19 am, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Basic Query, GROUP BY not working - finds those I don't

Post by simonmlewis »

Ooo ok.
Is "hasOperator" a special type of language or term in MySQL then? Never seen that.
The 'HAVING' set to 0, means that the group it finds for the session, shows NO 'operator' in the field, and therefore will product results.
Brilliant. Will try it a little later and let you know how I get on.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Basic Query, GROUP BY not working - finds those I don't

Post by VladSun »

simonmlewis wrote:Is "hasOperator" a special type of language or term in MySQL then? Never seen that.
No, nothing special - it's a column alias name of your choice :)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply