select values that do not have state = x

[James Watson]

I am currently writing a MySQL, Tomcat app and have some serious problems
figuring out a MySQL query. I know this is probably not the right place to
ask, since it focuses on java - but still it would be nice if somebody could
help me out here.

Suppose you have a table called testdata that looks like this:

mysql> desc test;
 
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| field_id | int(11) | YES  |     | NULL    |       |
| ref_id   | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
 

and has the following data:

mysql> select * from test;
+----------+--------+
| field_id | ref_id |
+----------+--------+
|      1   |      2 |
|      1   |      3 |
|      1   |      0 |
|      2   |      1 |
|      2   |      0 |
+----------+--------+

How do you manage to form a select that picks all field_ids that NEVER have the ref_id = 2?

In the above example my desired query would return my second field_id only (and only once),
because field_id=1 has one entry where it has ref_id=2.

I tried:
select distinct field_id,ref_id from test where ref_id not in (2)

and some similar queries with EXISTS and such...bot wasn't able to make it happen that way. Help me - please
[ June 28, 2005: Message edited by: James Watson ]

[Satish SN]

hi James !!

i did not get u r problem correctly

weather u want only those field_id whose ref_id is not 2 then the query should be select field_id where ref_id != 2

that will return all rows except the row with value as 2

or u want only those distinct field_ids like u have in ur table

u need to clearly tell ur problem then only u can expect good solution...

[Michael Matola]

How do you manage to form a select that picks all field_ids that NEVER have the ref_id = 2?

select distinct t1.field_id
from test t1
where not exists (
select 'x'
from test t2
where t2.field_id = t1.field_id
and t2.ref_id = '2')

[James Watson]

@Michael Matola
Thanks for the help, unfortunately I tried your query but wasnt able to get it to run. I did:

select distinct t1.field_id
from test t1
where not exists (
select field_id
from test t2
where t2.field_id = t1.field_id
and t2.ref_id = 2)


Then Mysql tells me:

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'exists (
select field_id
from test t2
where t2.field_id = t1.fi

I don't know anymore what to do, did I get your solution wrong?

@kumar satish

I will try to explain to you again:

Suppose you have a table, that has multiple entries with the same field_id value. Each entry has a different ref_id. I want to select all ocurrences
of field_id that never have the ref_id = 2.

[Paul Sturrock]

As far as I can see, Michael Matola's suggestion is valid for MySQL. Did you copy it correctly? You might also want to check your documentation as the error message suggests - is "exists" supported for the version you are using?

You could also solve your problem this way:

select * from test
where field_id not in (select field_id from test where ref_id = 2)

As an aside, I suspect you may be finding this difficult partly because your table does not define valid relational data, since it has no primary key constraint. Can you describe what you are trying to model?

[James Watson]

Thanks, that totally solved it!!! ;-)