Mysql: get records with no NULL values

[Boefje]

Hello,

I need to get all records from a table where a for a given playerid no
field enddate exists with value NULL.

table player_team:

id, playerid, startdate, enddate

1, 277, 2003-09-14 00:00:00, NULL
2, 278, 2003-09-14 00:00:00, NULL
3, 279, 2003-09-14 00:00:00, NULL
4, 280, 2003-09-14 00:00:00, NULL
5, 281, 2003-09-14 00:00:00, 2004-03-11 17:30:00
6, 282, 2003-09-14 00:00:00, 2004-03-11 17:30:00
7, 281, 2004-03-11 18:03:45, NULL
8, 282, 2004-03-12 12:58:13, 2004-03-12 13:02:00
9, 282, 2004-03-12 13:02:55, 2004-03-12 13:07:20

So the records where the enddate is not NULL are
id=5, playerid=281
id=6, playerid=282
id=8, playerid=282
id=9, playerid=282

I only want to get playerid 282 back because there exists no record
with this playerid where enddate is NULL


So it must be something like this:

SELECT
playerid
FROM
speler_team
WHERE
COUNT(enddate IS NULL)=0
GROUP BY
spelerid

This does not work. It comes up with an "Invalid use of group
function" error. How to do this?

Thanks for your help,

[Geoff Berrow]

Re: Mysql: get records with no NULL values

I noticed that Message-ID: <cad3509eluufrb 78t9ec3en08l4ht er68m@4ax.com>
from Boefje < B_o_e_f_j_e@Hot mail.com (remove the underscores)>
contained the following:
[color=blue]
>
>This does not work. It comes up with an "Invalid use of group
>function" error. How to do this?[/color]

Check out DISTINCT

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Mark Kuiphuis]

Re: Mysql: get records with no NULL values

Check your SQL-statement!!!
GROUP BY spelerid!!!

As far as I can see, the column 'spelerid' doesn't exist in your table.
Shouldn't that be the column: 'playerid'

(haven't checked the SQL-statement any further...)

Mark
[color=blue]
> Hello,
>
> I need to get all records from a table where a for a given playerid no
> field enddate exists with value NULL.
>
> table player_team:
>
> id, playerid, startdate, enddate
>
> 1, 277, 2003-09-14 00:00:00, NULL
> 2, 278, 2003-09-14 00:00:00, NULL
> 3, 279, 2003-09-14 00:00:00, NULL
> 4, 280, 2003-09-14 00:00:00, NULL
> 5, 281, 2003-09-14 00:00:00, 2004-03-11 17:30:00
> 6, 282, 2003-09-14 00:00:00, 2004-03-11 17:30:00
> 7, 281, 2004-03-11 18:03:45, NULL
> 8, 282, 2004-03-12 12:58:13, 2004-03-12 13:02:00
> 9, 282, 2004-03-12 13:02:55, 2004-03-12 13:07:20
>
> So the records where the enddate is not NULL are
> id=5, playerid=281
> id=6, playerid=282
> id=8, playerid=282
> id=9, playerid=282
>
> I only want to get playerid 282 back because there exists no record
> with this playerid where enddate is NULL
>
>
> So it must be something like this:
>
> SELECT
> playerid
> FROM
> speler_team
> WHERE
> COUNT(enddate IS NULL)=0
> GROUP BY
> spelerid
>
> This does not work. It comes up with an "Invalid use of group
> function" error. How to do this?
>
> Thanks for your help,
>[/color]

[Boefje]

Re: Mysql: get records with no NULL values

On Fri, 12 Mar 2004 14:16:59 +0100, Mark Kuiphuis
<maluka@koekelo ekoe.nl> wrote:
[color=blue]
>Check your SQL-statement!!!
> GROUP BY spelerid!!!
>
>As far as I can see, the column 'spelerid' doesn't exist in your table.
>Shouldn't that be the column: 'playerid'
>
>(haven't checked the SQL-statement any further...)
>
>Mark
>[color=green]
>> Hello,
>>
>> I need to get all records from a table where a for a given playerid no
>> field enddate exists with value NULL.
>>
>> table player_team:
>>
>> id, playerid, startdate, enddate
>>
>> 1, 277, 2003-09-14 00:00:00, NULL
>> 2, 278, 2003-09-14 00:00:00, NULL
>> 3, 279, 2003-09-14 00:00:00, NULL
>> 4, 280, 2003-09-14 00:00:00, NULL
>> 5, 281, 2003-09-14 00:00:00, 2004-03-11 17:30:00
>> 6, 282, 2003-09-14 00:00:00, 2004-03-11 17:30:00
>> 7, 281, 2004-03-11 18:03:45, NULL
>> 8, 282, 2004-03-12 12:58:13, 2004-03-12 13:02:00
>> 9, 282, 2004-03-12 13:02:55, 2004-03-12 13:07:20
>>
>> So the records where the enddate is not NULL are
>> id=5, playerid=281
>> id=6, playerid=282
>> id=8, playerid=282
>> id=9, playerid=282
>>
>> I only want to get playerid 282 back because there exists no record
>> with this playerid where enddate is NULL
>>
>>
>> So it must be something like this:
>>
>> SELECT
>> playerid
>> FROM
>> speler_team
>> WHERE
>> COUNT(enddate IS NULL)=0
>> GROUP BY
>> spelerid
>>
>> This does not work. It comes up with an "Invalid use of group
>> function" error. How to do this?
>>
>> Thanks for your help,
>>[/color][/color]
The DISTINCT remark won't help me any further, because it will only
remove duplicates. That's not what I need.

Mark, of course you are right. I translated the field names in my
query to have a more clear example. So I made it worse. Damm, I hate
it when I make these silly mistakes.

spelerid has to be playerid.

Anyhow, I still need help!

[Mark Kuiphuis]

Re: Mysql: get records with no NULL values

Together with PHP you can accomplish this in two steps.

First execute this SQL-statement, which filters only the playerid's who
have dates that are NOT NULL. Then store these player id's in an array
(or in the while loop of the first SQL-result) and execute the 2nd SQL
statement. If the mysql_num_rows == 0 then you know that this playerid
has no empty enddates

SQL 1: SELECT DISTINCT playerid FROM player_team WHERE enddate != ''
SQL 2: SELECT playerid FROM player_team WHERE enddate = ''

little example:

$query1 = "SELECT DISTINCT playerid FROM player_team WHERE enddate != ''";
$result1 = mysql_query($qu ery1);
if($result1) {
while($r1 = mysql_fetch_arr ay($result1)) {
$playerid = $r1["playerid"];

$query2 = "SELECT playerid FROM player_team WHERE enddate = ''";
$result2 = mysql_query($qu ery2);
if (mysql_num_rows ($result2) == 0) {
// this playerid has no empty dates
// we store this playerid in an array....
$players[] = $playerid;
}
}
}

Mark

Boefje wrote:[color=blue]
> On Fri, 12 Mar 2004 14:16:59 +0100, Mark Kuiphuis
> <maluka@koekelo ekoe.nl> wrote:
>
>[color=green]
>>Check your SQL-statement!!!
>> GROUP BY spelerid!!!
>>
>>As far as I can see, the column 'spelerid' doesn't exist in your table.
>>Shouldn't that be the column: 'playerid'
>>
>>(haven't checked the SQL-statement any further...)
>>
>>Mark
>>
>>[color=darkred]
>>>Hello,
>>>
>>>I need to get all records from a table where a for a given playerid no
>>>field enddate exists with value NULL.
>>>
>>>table player_team:
>>>
>>>id, playerid, startdate, enddate
>>>
>>>1, 277, 2003-09-14 00:00:00, NULL
>>>2, 278, 2003-09-14 00:00:00, NULL
>>>3, 279, 2003-09-14 00:00:00, NULL
>>>4, 280, 2003-09-14 00:00:00, NULL
>>>5, 281, 2003-09-14 00:00:00, 2004-03-11 17:30:00
>>>6, 282, 2003-09-14 00:00:00, 2004-03-11 17:30:00
>>>7, 281, 2004-03-11 18:03:45, NULL
>>>8, 282, 2004-03-12 12:58:13, 2004-03-12 13:02:00
>>>9, 282, 2004-03-12 13:02:55, 2004-03-12 13:07:20
>>>
>>>So the records where the enddate is not NULL are
>>>id=5, playerid=281
>>>id=6, playerid=282
>>>id=8, playerid=282
>>>id=9, playerid=282
>>>
>>>I only want to get playerid 282 back because there exists no record
>>>with this playerid where enddate is NULL
>>>
>>>
>>>So it must be something like this:
>>>
>>>SELECT
>>> playerid
>>>FROM
>>> speler_team
>>>WHERE
>>> COUNT(enddate IS NULL)=0
>>>GROUP BY
>>> spelerid
>>>
>>>This does not work. It comes up with an "Invalid use of group
>>>function" error. How to do this?
>>>
>>>Thanks for your help,
>>>[/color][/color]
>
> The DISTINCT remark won't help me any further, because it will only
> remove duplicates. That's not what I need.
>
> Mark, of course you are right. I translated the field names in my
> query to have a more clear example. So I made it worse. Damm, I hate
> it when I make these silly mistakes.
>
> spelerid has to be playerid.
>
> Anyhow, I still need help!
>
>[/color]

[David Mackenzie]

Re: Mysql: get records with no NULL values

On Fri, 12 Mar 2004 14:04:28 +0100, Boefje < B_o_e_f_j_e@Hot mail.com
(remove the underscores)> wrote:
[color=blue]
>table player_team:
>
>id, playerid, startdate, enddate
>
>1, 277, 2003-09-14 00:00:00, NULL
>2, 278, 2003-09-14 00:00:00, NULL
>3, 279, 2003-09-14 00:00:00, NULL
>4, 280, 2003-09-14 00:00:00, NULL
>5, 281, 2003-09-14 00:00:00, 2004-03-11 17:30:00
>6, 282, 2003-09-14 00:00:00, 2004-03-11 17:30:00
>7, 281, 2004-03-11 18:03:45, NULL
>8, 282, 2004-03-12 12:58:13, 2004-03-12 13:02:00
>9, 282, 2004-03-12 13:02:55, 2004-03-12 13:07:20[/color]
[color=blue]
>I only want to get playerid 282 back because there exists no record
>with this playerid where enddate is NULL[/color]

The count() function ignores NULL values, so this query returns only
the groups where the count of player id is equal to the count of
enddate (i.e. has no nulls):

SELECT playerid
FROM player_team
GROUP BY playerid
HAVING count(enddate) = count(playerid)

No need for DISTINCT as GROUP BY does that anyway.

If you need restrict your result based on the result of an aggregate
function (count, min, max, etc), use the HAVING clause.

WHERE limits the result set before GROUP BY does its work. HAVING
limits the result after the GROUP BY has taken place.

--
David ( @priz.co.uk )

[Mark Kuiphuis]

Re: Mysql: get records with no NULL values

Sorry forgot to add "AND playerid = '$playerid' in SQL 2;

SQL 2 will become:
SELECT playerid FROM player_team WHERE enddate = '' AND playerid =
'$playerid';

Mark

Mark Kuiphuis wrote:
[color=blue]
> Together with PHP you can accomplish this in two steps.
>
> First execute this SQL-statement, which filters only the playerid's who
> have dates that are NOT NULL. Then store these player id's in an array
> (or in the while loop of the first SQL-result) and execute the 2nd SQL
> statement. If the mysql_num_rows == 0 then you know that this playerid
> has no empty enddates
>
> SQL 1: SELECT DISTINCT playerid FROM player_team WHERE enddate != ''
> SQL 2: SELECT playerid FROM player_team WHERE enddate = ''
>
> little example:
>
> $query1 = "SELECT DISTINCT playerid FROM player_team WHERE enddate != ''";
> $result1 = mysql_query($qu ery1);
> if($result1) {
> while($r1 = mysql_fetch_arr ay($result1)) {
> $playerid = $r1["playerid"];
>
> $query2 = "SELECT playerid FROM player_team WHERE enddate = ''";
> $result2 = mysql_query($qu ery2);
> if (mysql_num_rows ($result2) == 0) {
> // this playerid has no empty dates
> // we store this playerid in an array....
> $players[] = $playerid;
> }
> }
> }
>
> Mark
>
> Boefje wrote:
>[color=green]
>> On Fri, 12 Mar 2004 14:16:59 +0100, Mark Kuiphuis
>> <maluka@koekelo ekoe.nl> wrote:
>>
>>[color=darkred]
>>> Check your SQL-statement!!!
>>> GROUP BY spelerid!!!
>>>
>>> As far as I can see, the column 'spelerid' doesn't exist in your
>>> table. Shouldn't that be the column: 'playerid'
>>>
>>> (haven't checked the SQL-statement any further...)
>>>
>>> Mark
>>>
>>>
>>>> Hello,
>>>>
>>>> I need to get all records from a table where a for a given playerid no
>>>> field enddate exists with value NULL.
>>>>
>>>> table player_team:
>>>>
>>>> id, playerid, startdate, enddate
>>>> 1, 277, 2003-09-14 00:00:00, NULL 2, 278,
>>>> 2003-09-14 00:00:00, NULL 3, 279, 2003-09-14
>>>> 00:00:00, NULL 4, 280, 2003-09-14 00:00:00, NULL
>>>> 5, 281, 2003-09-14 00:00:00, 2004-03-11 17:30:00
>>>> 6, 282, 2003-09-14 00:00:00, 2004-03-11 17:30:00
>>>> 7, 281, 2004-03-11 18:03:45, NULL 8, 282,
>>>> 2004-03-12 12:58:13, 2004-03-12 13:02:00 9, 282,
>>>> 2004-03-12 13:02:55, 2004-03-12 13:07:20
>>>> So the records where the enddate is not NULL are id=5, playerid=281
>>>> id=6, playerid=282
>>>> id=8, playerid=282
>>>> id=9, playerid=282
>>>>
>>>> I only want to get playerid 282 back because there exists no record
>>>> with this playerid where enddate is NULL
>>>>
>>>>
>>>> So it must be something like this:
>>>>
>>>> SELECT playerid FROM speler_team WHERE COUNT(enddate IS NULL)=0
>>>> GROUP BY spelerid
>>>>
>>>> This does not work. It comes up with an "Invalid use of group
>>>> function" error. How to do this?
>>>>
>>>> Thanks for your help,
>>>>[/color]
>>
>> The DISTINCT remark won't help me any further, because it will only
>> remove duplicates. That's not what I need.
>>
>> Mark, of course you are right. I translated the field names in my
>> query to have a more clear example. So I made it worse. Damm, I hate
>> it when I make these silly mistakes.
>>
>> spelerid has to be playerid.
>> Anyhow, I still need help!
>>
>>[/color][/color]

[Boefje]

Re: Mysql: get records with no NULL values

Mark thanks. I hoped I could do it with one query preferably without
using JOIN. I am not exactly a SQL guru.

But it costs me to much time, so I will do it with two different
SELECTS as you proposed.

Cheers
GB





On Fri, 12 Mar 2004 15:39:17 +0100, Mark Kuiphuis
<maluka@remove_ this.koekeloeko e.nl> wrote:
[color=blue]
>Together with PHP you can accomplish this in two steps.
>
>First execute this SQL-statement, which filters only the playerid's who
>have dates that are NOT NULL. Then store these player id's in an array
>(or in the while loop of the first SQL-result) and execute the 2nd SQL
>statement. If the mysql_num_rows == 0 then you know that this playerid
>has no empty enddates
>
>SQL 1: SELECT DISTINCT playerid FROM player_team WHERE enddate != ''
>SQL 2: SELECT playerid FROM player_team WHERE enddate = ''
>
>little example:
>
>$query1 = "SELECT DISTINCT playerid FROM player_team WHERE enddate != ''";
>$result1 = mysql_query($qu ery1);
>if($result1) {
> while($r1 = mysql_fetch_arr ay($result1)) {
> $playerid = $r1["playerid"];
>
> $query2 = "SELECT playerid FROM player_team WHERE enddate = ''";
> $result2 = mysql_query($qu ery2);
> if (mysql_num_rows ($result2) == 0) {
> // this playerid has no empty dates
> // we store this playerid in an array....
> $players[] = $playerid;
> }
> }
>}
>
>Mark[/color]

[Mark Kuiphuis]

Re: Mysql: get records with no NULL values

No problem...

I am not a SQL guru myself either, that's why I solved it this way :P

Mark

Boefje wrote:
[color=blue]
> Mark thanks. I hoped I could do it with one query preferably without
> using JOIN. I am not exactly a SQL guru.
>
> But it costs me to much time, so I will do it with two different
> SELECTS as you proposed.
>
> Cheers
> GB
>
>
>
>
>
> On Fri, 12 Mar 2004 15:39:17 +0100, Mark Kuiphuis
> <maluka@remove_ this.koekeloeko e.nl> wrote:
>
>[color=green]
>>Together with PHP you can accomplish this in two steps.
>>
>>First execute this SQL-statement, which filters only the playerid's who
>>have dates that are NOT NULL. Then store these player id's in an array
>>(or in the while loop of the first SQL-result) and execute the 2nd SQL
>>statement. If the mysql_num_rows == 0 then you know that this playerid
>>has no empty enddates
>>
>>SQL 1: SELECT DISTINCT playerid FROM player_team WHERE enddate != ''
>>SQL 2: SELECT playerid FROM player_team WHERE enddate = ''
>>
>>little example:
>>
>>$query1 = "SELECT DISTINCT playerid FROM player_team WHERE enddate != ''";
>>$result1 = mysql_query($qu ery1);
>>if($result1 ) {
>> while($r1 = mysql_fetch_arr ay($result1)) {
>> $playerid = $r1["playerid"];
>>
>> $query2 = "SELECT playerid FROM player_team WHERE enddate = ''";
>> $result2 = mysql_query($qu ery2);
>> if (mysql_num_rows ($result2) == 0) {
>> // this playerid has no empty dates
>> // we store this playerid in an array....
>> $players[] = $playerid;
>> }
>> }
>>}
>>
>>Mark[/color]
>
>[/color]