Page 1 of 1

Query problem involving IN

Posted: Wed Nov 12, 2003 10:40 am
by yaron
Hello all,

I have a query which I need to get records from 2 tables.
The records I need to pull out must need to be unequal in a specific field.
But there are records that that specific field value is both equal and unequal i.e. 2 or more rows.
If there is an equal record I don't need to retrieve the unequal rows...
here is an example of what I wrote

Code: Select all

<?php
SELECT tbl1.field1 ,tbl1.filed2,tbl1.field3,tbl2.field2 FROM tbl1,tbl2 WHERE tbl1.field1=tbl2.field1 AND tbl1.field2=tbl2.field2 AND tbl1.field3<>tbl2.field3 AND tbl1.field3 NOT IN (SELECT SELECT tbl1.field1 ,tbl1.filed2,tbl1.field3,tbl2.field2 FROM tbl1,tbl2 WHERE tbl1.field1=tbl2.field1 AND tbl1.field2=tbl2.field2 AND tbl1.field3=tbl2.field3);
?>
My query is a bit longer that this one but the idea is clear I hope..
I get a syntax error on this..
any ideas?
thanks

Posted: Wed Nov 12, 2003 10:50 am
by JayBird
You have 2 SELECT functions at the start of your open bracket.

Is that the problem?

Mark

Posted: Wed Nov 12, 2003 11:01 am
by yaron
no no,
it's from the great wonders of the copy&paste technique.
:-)

Posted: Thu Nov 13, 2003 2:49 am
by twigletmac
Which database are you using and which version?

Mac

Posted: Thu Nov 13, 2003 4:21 am
by yaron
I'm using mysql 3.23.47

Posted: Thu Nov 13, 2003 5:49 am
by twigletmac
You can't use sub-selects then, do a search on the manual:
http://www.mysql.com/doc

Mac

Posted: Thu Nov 13, 2003 6:35 am
by yaron
hmm...
So is there another solution to my problem?

Posted: Thu Nov 13, 2003 6:41 am
by yaron
is there a mysql version that is able to use this?

Posted: Thu Nov 13, 2003 8:49 am
by twigletmac
Please search the manual - it has the answers to both your questions.

Mac

Posted: Thu Nov 13, 2003 9:10 am
by Weirdan
seems like your problem can be solved with the following query:

Code: Select all

select * from t1,t2 group by t1.f having max(t1.f=t2.f)=0
Play with it a bit.