hi peter....
i must be missing something. the following is my actual schema. i have a
test tbl with ~2900 rows... only a few of the rows have an actionID=3. each
universityID can have multiple actionIDs
mysql> describe SvnTBL;
+--------------+----------+------+-----+----------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------+------+-----+---------------+----------------+
| universityID | int | NO | | 0 | |
| actionID | int | NO | | 0 | |
| statusID | int | NO | | 0 | |
| _date | timestamp| YES | | CURRENT_TIMESTAMP |
|
| ID | int | NO | PRI | NULL | auto_increment |
| semseterID | int | NO | | 0 | |
+--------------+-----------+------+-----+---------------+----------------+
6 rows in set (0.09 sec)
when i do:
select distinct universityID, from SvnTBL
where actionID !=3;
i get return of 2879 rows,
which is the same thing i get when i do:
select distinct universityID, from SvnTBL;
when i do:
mysql> select universityID, actionID from SvnTBL
-> where actionID =3;
+--------------+----------+
| universityID | actionID |
+--------------+----------+
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
+--------------+----------+
3 rows in set (0.00 sec)
which tells me that i have 3 'groups' (on universityID) that have
actionID=3. however, each of these universityID, can also have
actionID=(1,2) as well.
so how can a query be created to return the universityID (groups) that don't
have an actionID=3...
when i tried,
SELECT DISTINCT universityID
FROM SvnTBL
WHERE actionID != 3
i got the same as if i did:
SELECT DISTINCT universityID
FROM SvnTBL;
thanks..
-----Original Message-----
From: Peter Bradley [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 04, 2007 9:32 AM
To: [EMAIL PROTECTED]
Cc: 'Chris White'; mysql@lists.mysql.com
Subject: Re: group by/select issue..
Bruce,
Try:
SELECT DISTINCT NAME
FROM DOG
WHERE STATUS != 3
Should do the trick.
You obviously don't want the STATUS field. If you include it, you'll
get more than one line per name. Similarly for ID. If you want to
include the STATUS or ID fields, then you obviously want more than one
line (otherwise what would you expect to go in there?).
HTH
Peter
Ysgrifennodd bruce:
> hi chris...
>
> your query,
> >>SELECT name FROM dog WHERE status = 3 GROUP BY name<<
>
> will actually give the items where status=3
>
> however, i can't get the resulting issues by doing 'status!=3', because
the
> tbl has multiple status for a given name, so the query will still return
the
> other status that aren't equal to '3' for the given name...
>
>
>
> -----Original Message-----
> From: Chris White [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 04, 2007 9:07 AM
> To: [EMAIL PROTECTED]
> Cc: mysql@lists.mysql.com
> Subject: Re: group by/select issue..
>
>
> bruce wrote:
>
>> i'm trying to figure out how to create a select query that groups
>> the tbl around 'name' such that if i want all names that do not
>> have a status=3, i'd get a single row for 'sue' and 'bob'
>>
>
> I'm not sure why `SELECT name FROM dog WHERE status = 3 GROUP BY name;`
> wouldn't give you what you'd want (or that's possibly what you're
> looking for?). If that's the answer then "wee", if not I'll throw my
> lost flag in the air.
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
