mysql> select distinct a.* from tableA a left join tableB b1 using(id), tableB b2 where a.id=b1.id && b1.id=1 && b2.id=3;
-----Original Message----- From: Steen Rabol [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 31, 2002 2:51 PM To: Rick Emery; [EMAIL PROTECTED] Subject: Re: Is this possible in one SQL statement I want to select all the records in table A which have record in Table B where field1 is equal to 3 and have a record in Table B where field1 is equal to 1, in other word two records (or more) must exist in Table B in order to give me a record from Table A like this select * from table_a, tabele_b.field1 where table_a.id = table_b.id and (table_b.filed1 = 1 AND able_b.filed1 = 3) ^^^^ This is the 'problem' as I perfectly know that a field only can contain one value Hope this is more clear. Best Steen ----- Original Message ----- From: "Rick Emery" <[EMAIL PROTECTED]> To: "'Steen Rabol'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, January 31, 2002 9:35 PM Subject: RE: Is this possible in one SQL statement > Please restate the following more completely. I think some typos got in > there. If not, then we need more detail of what you really want. > > the condition is then value 1 and 3 and the record I want is A1 and A3 due > to the fact that a recorc exists in table B with a value of on and one > record with the value of 3 > > -----Original Message----- > From: Steen Rabol [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 31, 2002 2:25 PM > To: [EMAIL PROTECTED] > Subject: Is this possible in one SQL statement > > > Is this possible in a single select statement ? > > table A id (Unique), a_field1,a_field2,a_field3 > table B id(NOT unique), b_field1 > > Id in table B is a ref to id in table A > > Now i would like to select all the id's from table A which have records i > table B where b_field1 is 1 and b_field2 is 2 > > example: > > Table a record: 1, "text","text","text" - A1 > Table a record: 2, "text","text","text" - A2 > Table a record: 3, "text","text","text" - A3 > > Table b record 1, 1 > Table b record 2, 2 > Table b record 1, 3 > Table b record 2, 1 > Table b record 1, 4 > Table b record 3, 1 > Table b record 3, 3 > > the condition is then value 1 and 3 and the record I want is A1 and A3 due > to the fact that a recorc exists in table B with a value of on and one > record with the value of 3 > > Is it clean ;-)) > > > I know that it can be done with a nested loops, but is it possible in one > statement sql query ? > > Thanks in advance > > Steen > > > > --------------------------------------------------------------------- > 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 > > --------------------------------------------------------------------- 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