Use a derived table (untested query):
select distinct universityID
from SvnTBL s1
left outer join
(select universityID from SvnTBL where actionID =3) as s2 ON
s1.universityID=s2.universityID
where s2.university ID is NULL
I'm not sure if derived tables are in all versions of MySQL, I use MySQL
5.0. If your tables are big, you'll probably need to add an index on
universityID.
Hope that helps.
Donna
"bruce" <[EMAIL PROTECTED]>
01/04/2007 12:49 PM
Please respond to
<[EMAIL PROTECTED]>
To
"'Peter Bradley'" <[EMAIL PROTECTED]>
cc
"'Chris White'" <[EMAIL PROTECTED]>, <mysql@lists.mysql.com>
Subject
RE: group by/select issue..
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]
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
CONFIDENTIALITY NOTICE:This email is intended solely for the person or
entity to which it is addressed and may contain confidential and/or
protected health information. Any duplication, dissemination, action
taken in reliance upon, or other use of this information by persons or
entities other than the intended recipient is prohibited and may violate
applicable laws. If this email has been received in error, please notify
the sender and delete the information from your system. The views
expressed in this email are those of the sender and may not necessarily
represent the views of IntelliCare.
