wood burning stoves 2.0*
The moose likes JDBC and the fly likes select values that do not have state = x Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "select values that do not have state = x" Watch "select values that do not have state = x" New topic
Author

select values that do not have state = x

James Watson
Greenhorn

Joined: Jun 28, 2005
Posts: 3
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:

and has the following data:

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
Ranch Hand

Joined: Apr 19, 2005
Posts: 70
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...


Satish SN<br />SCJP 1.4 & SCWCD 1.4
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
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
Greenhorn

Joined: Jun 28, 2005
Posts: 3
@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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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:


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?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
James Watson
Greenhorn

Joined: Jun 28, 2005
Posts: 3
Thanks, that totally solved it!!! ;-)
 
 
subject: select values that do not have state = x