473,322 Members | 1,494 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Mysql: get records with no NULL values

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,

Jul 17 '05 #1
8 3920
I noticed that Message-ID: <ca********************************@4ax.com>
from Boefje < B_*********@Hotmail.com (remove the underscores)>
contained the following:

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


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/
Jul 17 '05 #2
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,

Jul 17 '05 #3
On Fri, 12 Mar 2004 14:16:59 +0100, Mark Kuiphuis
<ma****@koekeloekoe.nl> wrote:
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,

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!
Jul 17 '05 #4
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($query1);
if($result1) {
while($r1 = mysql_fetch_array($result1)) {
$playerid = $r1["playerid"];

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

Mark

Boefje wrote:
On Fri, 12 Mar 2004 14:16:59 +0100, Mark Kuiphuis
<ma****@koekeloekoe.nl> wrote:

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,


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!

Jul 17 '05 #5
On Fri, 12 Mar 2004 14:04:28 +0100, Boefje < B_*********@Hotmail.com
(remove the underscores)> wrote:
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 I only want to get playerid 282 back because there exists no record
with this playerid where enddate is NULL


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 )
Jul 17 '05 #6
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:
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($query1);
if($result1) {
while($r1 = mysql_fetch_array($result1)) {
$playerid = $r1["playerid"];

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

Mark

Boefje wrote:
On Fri, 12 Mar 2004 14:16:59 +0100, Mark Kuiphuis
<ma****@koekeloekoe.nl> wrote:

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,


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!

Jul 17 '05 #7
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.koekeloekoe.nl> wrote:
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($query1);
if($result1) {
while($r1 = mysql_fetch_array($result1)) {
$playerid = $r1["playerid"];

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

Mark


Jul 17 '05 #8
No problem...

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

Mark

Boefje wrote:
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.koekeloekoe.nl> wrote:

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($query1);
if($result1) {
while($r1 = mysql_fetch_array($result1)) {
$playerid = $r1["playerid"];

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

Mark


Jul 17 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Bob Bedford | last post by:
I've a table on mysql: code1 tinyint, code2 tinyint, notcode1 varchar(20),notcode2 varchar(20),label I do have some records: values(1,null,null,null,'text1');...
0
by: David Bordas | last post by:
Hi list, I've got a little bug with MySQL. I can insert a row into my table but this row will not appear in the table :( Server is under linux redhat, MySQL is 3.23.56 installed from binary...
0
by: Frank | last post by:
This one has me pretty stumped at the moment. I've tried variations with MySQL IF() statements but can't quite get it right. How can I select all of the records of the table 'items' that are...
0
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest...
6
by: Ljoha | last post by:
I need to create report where will be shown total quantity of all rows for every hour in some data range. I have a table where I have column in DATETIME format. I have problem to create sql...
10
by: Python_it | last post by:
Python 2.4 MySQL-python.exe-1.2.0.win32-py2.4.zip How can I insert a NULL value in a table (MySQL-database). I can't set a var to NULL? Or is there a other possibility? My var must be variable...
0
by: hahahardididi | last post by:
Hi Forums, I have a frustrating problem on my Stored Procedure. It can only proccess about 100 records in 10 minutes. I have 2 million initial records that need to processed. Meaning that with...
15
by: l3vi | last post by:
I have a new system Im building that stores entries of what people are searching for on my sites. I want to be able to keep records of how many times a keyword was searched for daily, and from...
5
by: RioRanchoMan | last post by:
I have a forum table where the field Forum_ID of the first thread corresponds to itself in the field Forum_Ancestor, and 0 (zero) for the field Forum_Parent when it is the first topic in a thread:...
0
by: nrip | last post by:
Dear All, I am facing a very peculiar problem. I am reading a CSV file from my JSP code and trying to insert them into MYSQL database. the program first reads a line and then splits it into words...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.