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
