query question...

hi...

i'm looking at what is probably a basic question.

i have a tbl with
-id
-action
-status
-date

ie:
id action status date
1 0 1
1 1 2
1 2 3
---------------------------------
2 0 4
2 2 5


i need a query to somehow get all id's that don't have an 'action=1', in
this case, id '2' would be the id that should be returned from the query...

however, i can't quite figure out how to create a query to select the items
that are the ones i'm looking for.

any help/thoughts would be appreciated!

thanks

-bruce

bruce wrote:
> hi...
>
> i'm looking at what is probably a basic question.
>
> i have a tbl with
> -id
> -action
> -status
> -date
>
> ie:
> id action status date
> 1 0 1
> 1 1 2
> 1 2 3
> ---------------------------------
> 2 0 4
> 2 2 5
>
>
> i need a query to somehow get all id's that don't have an 'action=1', in
> this case, id '2' would be the id that should be returned from the query...
>
> however, i can't quite figure out how to create a query to select the items
> that are the ones i'm looking for.
>
> any help/thoughts would be appreciated!
>

select
t1.*
from
tbl t1
left join
tbl t2
on
t1.id = t2.id
where
t2.id is NULL &&
t2.action = 1

I *think*.

--
John C. Nichel IV
Programmer/System Admin
Dot Com Holdings of Buffalo
716.856.9675
jnichel@dotcomholdingsofbuffalo.com

Ysgrifennodd bruce:
> hi...
>
> i'm looking at what is probably a basic question.
>
> i have a tbl with
> -id
> -action
> -status
> -date
>
> ie:
> id action status date
> 1 0 1
> 1 1 2
> 1 2 3
> ---------------------------------
> 2 0 4
> 2 2 5
>
>
> i need a query to somehow get all id's that don't have an 'action=1', in
> this case, id '2' would be the id that should be returned from the query...
>
> however, i can't quite figure out how to create a query to select the items
> that are the ones i'm looking for.
>
> any help/thoughts would be appreciated!
>
> thanks
>
> -bruce
>
Hi Bruce,

Does this do it for you?

SELECT t1.id
FROM tbl t1
WHERE t1.id NOT IN (SELECT t2.id
FROM tbl t2
WHERE t2.id = 1)


Peter

Try this on for size:

SELECT DISTINCT id FROM tbl
WHERE id NOT IN (SELECT id FROM tbl WHERE action = 1)

The subselect will only work in 4.1 and later I think.

Dan


On 12/8/06, bruce <bedouglas@earthlink.net> wrote:
> hi...
>
> i'm looking at what is probably a basic question.
>
> i have a tbl with
> -id
> -action
> -status
> -date
>
> ie:
> id action status date
> 1 0 1
> 1 1 2
> 1 2 3
> ---------------------------------
> 2 0 4
> 2 2 5
>
>
> i need a query to somehow get all id's that don't have an 'action=1', in
> this case, id '2' would be the id that should be returned from the query...
>
> however, i can't quite figure out how to create a query to select the items
> that are the ones i'm looking for.
>
> any help/thoughts would be appreciated!
>
> thanks
>
> -bruce
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>

hi peter.

thanks, the solution you gave me is close...!!

my actual data is:
mysql> select * from SvnTBL;
+--------------+----------+----------+---------------------+----+
| universityID | actionID | statusID | _date | ID |
+--------------+----------+----------+---------------------+----+
| 1 | 1 | 0 | 2006-12-08 13:12:15 | 1 |
| 1 | 2 | 0 | 2006-12-08 13:12:15 | 2 |
| 1 | 3 | 0 | 2006-12-08 13:12:15 | 3 |
| 2 | 1 | 0 | 2006-12-08 13:12:15 | 4 |
| 2 | 3 | 0 | 2006-12-08 13:12:15 | 5 |
| 3 | 1 | 0 | 2006-12-08 13:12:15 | 6 |
| 3 | 6 | 0 | 2006-12-08 13:12:15 | 7 |
| 3 | 3 | 0 | 2006-12-08 13:12:15 | 8 |
| 3 | 4 | 0 | 2006-12-08 13:12:15 | 9 |
+--------------+----------+----------+---------------------+----+

if i do:
mysql> SELECT t1.universityID FROM SvnTBL t1 WHERE t1.universityID NOT IN
(SELECT t2.universityID FROM SvnTBL t2 WHERE t2.actionID = 2);

i get,

+--------------+
| universityID |
+--------------+
| 2 |
| 2 |
| 3 |
| 3 |
| 3 |
| 3 |
+--------------+
6 rows in set (0.00 sec)

what i really want to get is:
+--------------+
| universityID |
+--------------+
| 2 |
| 3 |
+--------------+

which would be the unique 'id's.....

i've tried to do a 'limit' and group, but i'm missing some thing...

thanks

-bruce


-----Original Message-----
From: Peter Bradley [mailto:P.Bradley@dsl.pipex.com]
Sent: Friday, December 08, 2006 12:26 PM
To: bedouglas@earthlink.net
Cc: mysql@lists.mysql.com
Subject: Re: query question...


Ysgrifennodd bruce:
> hi...
>
> i'm looking at what is probably a basic question.
>
> i have a tbl with
> -id
> -action
> -status
> -date
>
> ie:
> id action status date
> 1 0 1
> 1 1 2
> 1 2 3
> ---------------------------------
> 2 0 4
> 2 2 5
>
>
> i need a query to somehow get all id's that don't have an 'action=1', in
> this case, id '2' would be the id that should be returned from the
query...
>
> however, i can't quite figure out how to create a query to select the
items
> that are the ones i'm looking for.
>
> any help/thoughts would be appreciated!
>
> thanks
>
> -bruce
>
Hi Bruce,

Does this do it for you?

SELECT t1.id
FROM tbl t1
WHERE t1.id NOT IN (SELECT t2.id
FROM tbl t2
WHERE t2.id = 1)


Peter

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=b...@earthlink.net

Just add a DISTINCT:

SELECT DISTINCT t1.universityID FROM SvnTBL t1 WHERE t1.universityID NOT IN
(SELECT t2.universityID FROM SvnTBL t2 WHERE t2.actionID = 2);

Dan


On 12/8/06, bruce <bedouglas@earthlink.net> wrote:
> hi peter.
>
> thanks, the solution you gave me is close...!!
>
> my actual data is:
> mysql> select * from SvnTBL;
> +--------------+----------+----------+---------------------+----+
> | universityID | actionID | statusID | _date | ID |
> +--------------+----------+----------+---------------------+----+
> | 1 | 1 | 0 | 2006-12-08 13:12:15 | 1 |
> | 1 | 2 | 0 | 2006-12-08 13:12:15 | 2 |
> | 1 | 3 | 0 | 2006-12-08 13:12:15 | 3 |
> | 2 | 1 | 0 | 2006-12-08 13:12:15 | 4 |
> | 2 | 3 | 0 | 2006-12-08 13:12:15 | 5 |
> | 3 | 1 | 0 | 2006-12-08 13:12:15 | 6 |
> | 3 | 6 | 0 | 2006-12-08 13:12:15 | 7 |
> | 3 | 3 | 0 | 2006-12-08 13:12:15 | 8 |
> | 3 | 4 | 0 | 2006-12-08 13:12:15 | 9 |
> +--------------+----------+----------+---------------------+----+
>
> if i do:
> mysql> SELECT t1.universityID FROM SvnTBL t1 WHERE t1.universityID NOT IN
> (SELECT t2.universityID FROM SvnTBL t2 WHERE t2.actionID = 2);
>
> i get,
>
> +--------------+
> | universityID |
> +--------------+
> | 2 |
> | 2 |
> | 3 |
> | 3 |
> | 3 |
> | 3 |
> +--------------+
> 6 rows in set (0.00 sec)
>
> what i really want to get is:
> +--------------+
> | universityID |
> +--------------+
> | 2 |
> | 3 |
> +--------------+
>
> which would be the unique 'id's.....
>
> i've tried to do a 'limit' and group, but i'm missing some thing...
>
> thanks
>
> -bruce
>
>
> -----Original Message-----
> From: Peter Bradley [mailto:P.Bradley@dsl.pipex.com]
> Sent: Friday, December 08, 2006 12:26 PM
> To: bedouglas@earthlink.net
> Cc: mysql@lists.mysql.com
> Subject: Re: query question...
>
>
> Ysgrifennodd bruce:
> > hi...
> >
> > i'm looking at what is probably a basic question.
> >
> > i have a tbl with
> > -id
> > -action
> > -status
> > -date
> >
> > ie:
> > id action status date
> > 1 0 1
> > 1 1 2
> > 1 2 3
> > ---------------------------------
> > 2 0 4
> > 2 2 5
> >
> >
> > i need a query to somehow get all id's that don't have an 'action=1', in
> > this case, id '2' would be the id that should be returned from the
> query...
> >
> > however, i can't quite figure out how to create a query to select the
> items
> > that are the ones i'm looking for.
> >
> > any help/thoughts would be appreciated!
> >
> > thanks
> >
> > -bruce
> >
> Hi Bruce,
>
> Does this do it for you?
>
> SELECT t1.id
> FROM tbl t1
> WHERE t1.id NOT IN (SELECT t2.id
> FROM tbl t2
> WHERE t2.id = 1)
>
>
> Peter
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=b...@earthlink.net
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>

Ysgrifennodd bruce:
> hi peter.
>
> thanks, the solution you gave me is close...!!
>
> <snip>
> what i really want to get is:
> +--------------+
> | universityID |
> +--------------+
> | 2 |
> | 3 |
> +--------------+
>
> which would be the unique 'id's.....
>
> i've tried to do a 'limit' and group, but i'm missing some thing...
>
> thanks
>
> -bruce
>
SELECT DISTINCT ...


I didn't realise you only wanted the distinct values.


Peter

dan...

thanks!!! like a charm.. now for the other 200 queries i'm dealing with!!


-----Original Message-----
From: Dan Buettner [mailto:drbuettner@gmail.com]
Sent: Friday, December 08, 2006 1:40 PM
To: bedouglas@earthlink.net
Cc: Peter Bradley; mysql@lists.mysql.com
Subject: Re: RE: query question...


Just add a DISTINCT:

SELECT DISTINCT t1.universityID FROM SvnTBL t1 WHERE t1.universityID NOT IN
(SELECT t2.universityID FROM SvnTBL t2 WHERE t2.actionID = 2);

Dan


On 12/8/06, bruce <bedouglas@earthlink.net> wrote:
> hi peter.
>
> thanks, the solution you gave me is close...!!
>
> my actual data is:
> mysql> select * from SvnTBL;
> +--------------+----------+----------+---------------------+----+
> | universityID | actionID | statusID | _date | ID |
> +--------------+----------+----------+---------------------+----+
> | 1 | 1 | 0 | 2006-12-08 13:12:15 | 1 |
> | 1 | 2 | 0 | 2006-12-08 13:12:15 | 2 |
> | 1 | 3 | 0 | 2006-12-08 13:12:15 | 3 |
> | 2 | 1 | 0 | 2006-12-08 13:12:15 | 4 |
> | 2 | 3 | 0 | 2006-12-08 13:12:15 | 5 |
> | 3 | 1 | 0 | 2006-12-08 13:12:15 | 6 |
> | 3 | 6 | 0 | 2006-12-08 13:12:15 | 7 |
> | 3 | 3 | 0 | 2006-12-08 13:12:15 | 8 |
> | 3 | 4 | 0 | 2006-12-08 13:12:15 | 9 |
> +--------------+----------+----------+---------------------+----+
>
> if i do:
> mysql> SELECT t1.universityID FROM SvnTBL t1 WHERE t1.universityID NOT IN
> (SELECT t2.universityID FROM SvnTBL t2 WHERE t2.actionID = 2);
>
> i get,
>
> +--------------+
> | universityID |
> +--------------+
> | 2 |
> | 2 |
> | 3 |
> | 3 |
> | 3 |
> | 3 |
> +--------------+
> 6 rows in set (0.00 sec)
>
> what i really want to get is:
> +--------------+
> | universityID |
> +--------------+
> | 2 |
> | 3 |
> +--------------+
>
> which would be the unique 'id's.....
>
> i've tried to do a 'limit' and group, but i'm missing some thing...
>
> thanks
>
> -bruce
>
>
> -----Original Message-----
> From: Peter Bradley [mailto:P.Bradley@dsl.pipex.com]
> Sent: Friday, December 08, 2006 12:26 PM
> To: bedouglas@earthlink.net
> Cc: mysql@lists.mysql.com
> Subject: Re: query question...
>
>
> Ysgrifennodd bruce:
> > hi...
> >
> > i'm looking at what is probably a basic question.
> >
> > i have a tbl with
> > -id
> > -action
> > -status
> > -date
> >
> > ie:
> > id action status date
> > 1 0 1
> > 1 1 2
> > 1 2 3
> > ---------------------------------
> > 2 0 4
> > 2 2 5
> >
> >
> > i need a query to somehow get all id's that don't have an 'action=1', in
> > this case, id '2' would be the id that should be returned from the
> query...
> >
> > however, i can't quite figure out how to create a query to select the
> items
> > that are the ones i'm looking for.
> >
> > any help/thoughts would be appreciated!
> >
> > thanks
> >
> > -bruce
> >
> Hi Bruce,
>
> Does this do it for you?
>
> SELECT t1.id
> FROM tbl t1
> WHERE t1.id NOT IN (SELECT t2.id
> FROM tbl t2
> WHERE t2.id = 1)
>
>
> Peter
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=b...@earthlink.net
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>