Page 1 of 1

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

Posted: Thu Aug 11, 2011 6:04 am
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.

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

Posted: Thu Aug 11, 2011 8:26 am
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).

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

Posted: Thu Aug 11, 2011 8:31 am
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.

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

Posted: Thu Aug 11, 2011 8:45 am
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.

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

Posted: Fri Aug 12, 2011 2:43 am
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.

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

Posted: Fri Aug 12, 2011 4:58 am
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'.

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

Posted: Mon Aug 15, 2011 5:16 am
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.

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

Posted: Mon Aug 15, 2011 5:19 am
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.

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

Posted: Mon Aug 15, 2011 5:21 am
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 :)