aspose file tools*
The moose likes JDBC and the fly likes SQL statement question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL statement question" Watch "SQL statement question" New topic
Author

SQL statement question

Nawar Gailani
Ranch Hand

Joined: Feb 08, 2005
Posts: 46
Hi there,
I don't know if this is the right place, but I hope so.
I am using Access as a database and trying to execute a sql statement but without success.
The tables I have are:

Candidates
----------
cand_num (PK)
firstName
lastName
profession
phone
mobile
email
fileName

Studies
-------------
study_id (PK)
name

CandStudy
------------
cand_num (PK)
study_id (PK)

so they are two tables + one linking table. When I run this statement:

I get nothing, but there is one record who should appear. It gives me not correct results in whatever conditions using AND, when I replace AND with OR, it gives me right results!
Is this a bug in MS Access? or I should write my sql statement differently?
Thanks for any help

[ January 18, 2007: Message edited by: Nawar Gailani ]
[ January 23, 2007: Message edited by: Nawar Gailani ]
Nawar Gailani
Ranch Hand

Joined: Feb 08, 2005
Posts: 46
Ok I edited my post to make it clearer.
Also I exported the database to SQL server express and tested there, it was the same problem. I searched everywhere but couldn't find an answer.
I think many of you guys came to such a query, so I hope one of you can answer me as I think it will pop up a lot in front of me.
How can I select from table A according to conditions from table B while A and B are linked by table AB? I mean is there something wrong with my sql statement?
Is there something I should know about using AND in sql statement?
Thanks for any help or hint.
Arulanand Dayalan
Ranch Hand

Joined: Aug 10, 2005
Posts: 124
Hope this help...

select
a.*
from
Candidates a,
(select
c.cand_num , b.study_id
from
Studies b
left outer join
CandStudy c on b.study_id = c.study_id
where
b.name LIKE '%StudyOne%' or b.name LIKE '%StudyTwo%'
)
as
d where a.cand_num = d.cand_num

In your Sql you had used AND that means both StudyOne and StudyTwo should be present. The above query is an genral one. You can modify it based on your conditon.
Nawar Gailani
Ranch Hand

Joined: Feb 08, 2005
Posts: 46
Thanks Arulanand for your help.
Unfortunately, it gave me the same results. What puzzles me is that both queries work with the OR, but don't work with AND!
so when i write:
select
a.*
from
Candidates a,
(select
c.cand_num , b.study_id
from
Studies b
left outer join
CandStudy c on b.study_id = c.study_id
where
b.name LIKE '%StudyOne%' AND b.name LIKE '%StudyTwo%'
)
as
d where a.cand_num = d.cand_num

it returns empty table while there is one record that meet the criteria.
It's strange!
I'll try with MySQL and post the result.
Carol Enderlin
drifter
Ranch Hand

Joined: Oct 10, 2000
Posts: 1364
I'd suggest trying them out one at a time, figuring out which criteria it does not meet. Then try to figure out why.

1. where b.name LIKE '%StudyOne%'

2. where b.name LIKE '%StudyTwo%'

You are saying that one name should contain bothe StudyOne and StudyTwo, right?

It could be as simple as it is a "zero" intead of an upper case "oh".
Nawar Gailani
Ranch Hand

Joined: Feb 08, 2005
Posts: 46
Hi Carol,
I don't have a problem in retrieving when I don't use 'and'. The only problem is when i write 'Where <condition1> AND <condition2>'.
Where condition1, condition2 related to table b.
I will examine the database design and the relations carefully. Maybe I missed something in the design stage. I'll post if I reach something (for a reference).
Thanks all.
Virag Saksena
Ranch Hand

Joined: Nov 27, 2005
Posts: 71
Can you post the results of executing this query


<a href="http://www.auptyma.com" target="_blank" rel="nofollow">The Peak of Performance</a>
Nawar Gailani
Ranch Hand

Joined: Feb 08, 2005
Posts: 46
Hi Virag,
Sorry for the delay, I was on vacations.
I tried your query, but I had some errors, first Candidates table doesn't have study_id.
Anyway, I'm still looking for an explination!
What I found that 'AND' and 'OR' in my query don't actually function like I thought! for example:

Here 'AND' is concating the two strings to be like this condition:
WHERE Studies.name LIKE '%StudyOne%', which is not what I'm looking for!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL statement question