Query problem involving IN

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Query problem involving IN

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

You have 2 SELECT functions at the start of your open bracket.

Is that the problem?

Mark
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post by yaron »

no no,
it's from the great wonders of the copy&paste technique.
:-)
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Which database are you using and which version?

Mac
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post by yaron »

I'm using mysql 3.23.47
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

You can't use sub-selects then, do a search on the manual:
http://www.mysql.com/doc

Mac
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post by yaron »

hmm...
So is there another solution to my problem?
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post by yaron »

is there a mysql version that is able to use this?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Please search the manual - it has the answers to both your questions.

Mac
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
Post Reply