----- Original Message -----
From: "Søren Merser" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Sunday, March 05, 2006 9:12 AM
Subject: Help on sql statement (not MySQL specifik)
Hi
Could someone please help me out here?
TABLE:
RECNO | ID | TYPE
---------------------------------
1 | 1 | NULL
2 | 2 | 4 <-KEEP AS IS
3 | 2 | 6 <- DROP
4 | 3 | NULL <- KEEP AS IS
5 | 4 | NULL <-KEEP AS IS
6 | 5 | 1 <-NULL
7 | 5 | 3 <-DROP
8 | 6 | 7 <-NULL
9 | 6 | 3 <-DROP
What I neede is a SQL statement that for a given value of TYPE, lets say
4,
selects all the unique id's with TYPE = 4 when appropriate otherwise NULL
like:
SOLUTION:
RECNO | ID | TYPE
---------------------------------
1 | 1 | NULL
2 | 2 | 4
3 | 3 | NULL
4 | 4 | NULL
5 | 5 | NULL
6 | 6 | NULL
I know this isn't MySQL specifik but hope for someones help anyway :-)
Sorry for any inconvenience this may course users of the list
I've reread your question and example a couple of times but I'm still not
clear on what you want but maybe this will help until you can clarify the
question.
You use WHERE to limit your result set to only specific rows of the original
table. Therefore, if you want only rows where the type is 4, you say:
Select <column-list>
from <table-name>
where type = 4;
For example, if your table name was 'mytab' and you wanted the ID column in
the result, you'd write:
Select ID
from mytab
where type = 4;
Now, if the result showed many identical values in the columns of the result
set and you just want to know the unique values of ID that had a type of 4,
you add DISTINCT to the query:
select distinct ID
from mytab
where type = 4;
But that's where I get confused by your description of your problem. There
is only one row in your sample table that has a type of 4 so DISTINCT isn't
going to do anything for you; you'll get the same result with or without
DISTINCT, at least with the data you've shown.
I also don't understand where the nulls come in. Do you actually want to
update the data in your table permanently so that a null isn't null any
longer (or a non-null value is null)? Or do you want to _display_ a null
where something isn't null? I don't understand what your "DROP" and "LEAVE
AS IS" remarks mean.
Can you explain more fully what you are trying to do?
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
