Yep that worked! Yeah. Now I must go figure out the
count(1/(condition)) part to figure out how that works
exactly. Thanks for all the help.
-Davin
--- Braxton Robbason <[EMAIL PROTECTED]> wrote:
> howzabout
>
> SELECT Table1.id, Table1.name, count(1/(Table2.offon
> = 'off')) AS NOFF
> FROM Table1 , Table2
> WHERE Table1.id = Table2.id
> GROUP BY Table1.id, Table1.name
> HAVING NOFF=0;
>
> works for me:
> mysql> create table Table1 (id int, name
> varchar(10));
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> create table Table2 (id int, offon
> varchar(10));
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into Table1 values(1,'red');
> Query OK, 1 row affected (0.01 sec)
>
> mysql> insert into Table1 values(2,'blue');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert into Table2 values(1,'off');
> Query OK, 1 row affected (0.01 sec)
>
> mysql> insert into Table2 values(1,'on');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert into Table2 values(2,'on');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> SELECT Table1.id, Table1.name,
> count(1/(Table2.offon = 'off')) AS
> NOFF
> -> FROM Table1 , Table2
> -> WHERE Table1.id = Table2.id
> -> GROUP BY Table1.id, Table1.name
> -> HAVING NOFF=0;
> +------+------+------+
> | id | name | NOFF |
> +------+------+------+
> | 2 | blue | 0 |
> +------+------+------+
> 1 row in set (0.00 sec)
>
>
> -----Original Message-----
> From: David Block [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, April 26, 2001 6:39 PM
> To: Opec Kemp
> Cc: Davin Flatten; Mysql Mailing List
> Subject: RE: Newbie question - Hopefully not too
> stupid!
>
>
> On Fri, 27 Apr 2001, Opec Kemp wrote:
>
> > Hi,
> > Have you tried:
> >
> > SELECT Table1.* FROM Table1 , Table2
> > WHERE Table1.id = Table2.id AND Table2.offon =
> 'on'
>
> That was my original idea as well, but that would
> return Red, where Davin
> wants no Red since there is a Table2 row with an off
> corresponding to Red.
>
> I couldn't figure out the next step either. Good
> question!
>
> >
> > > Hello all!
> > >
> > > Here is what I am trying to do.
> > > I am trying to find only records
> > > that all have the same value for a
> > > specific field in a related table.
> > >
> > > A simplified example:
> > > Table1 has 2 fields - id, name
> > > Table2 has 2 fields - id, offon
> > >
> > > Given these sets:
> > >
> > > Table1
> > > id name
> > > -----------
> > > 1 Red
> > > 2 Blue
> > > 3 Green
> > >
> > > Table2
> > > id offon
> > > ------------
> > > 1 off
> > > 1 on
> > > 2 on
> > > 2 on
> > > 3 off
> > > 3 off
> > >
> > > I want to be able to select
> > > only the Colors that have only
> > > ALL ons relating to them. I
> > > do not want a combination of
> > > off, off or on, off etc...
> > >
> > > The only way I have been able
> > > to accomplish this was to count
> > > the number of instances for each
> > > table and save them in temp tables
> > > and then compare the results. There
> > > must be a better way to do this!
> > >
> > > Also if anyone would like to recommend
> > > an SQL reference or tutororial I would
> > > love to hear about it.
> > >
> > > Thanks in advance.
> > > -Davin
> > >
>
> -----------
> David Block
> [EMAIL PROTECTED]
> http://bioinfo.pbi.nrc.ca/dblock/wiki
> NRC Plant Biotechnology Institute
> Saskatoon, SK, Canada
>
>
>
---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list
> archive)
>
> To request this thread, e-mail
> <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
>
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
>
>
>
>
---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list
> archive)
>
> To request this thread, e-mail
> <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
>
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
>
__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
