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
