Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL statement question

 
Nawar Gailani
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 128
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1364
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 71
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you post the results of executing this query
 
Nawar Gailani
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic