Multiple Where Conditions 1

[canadatct]

I seem to be having some difficulty with a fairly easy select statement, and I'm hoping that some other pairs of eyes might be able to help spot what I'm doing wrong.

Here is my select statement:

Select Field1 from Table1 where Field2 = 1 and Field2 = 2

If I was to remove one of the where conditions (for example: " and Field2 = 2"), the query works, returning the matching records for the remaining where condition. As soon as I add multiple where conditions, it returns nothing.

Any ideas? Thanks in advance.

 

[Golom]

I guess the main comment is that I can't think of any value that Field2 may assume where it is simultaneously equal to both 1 AND 2.

Do you mean

Field2 = 1 OR Field2 = 2 ?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[PHV]

Perhaps quantic SQL ? ;-)
You wanted this ?
SELECT Field1 FROM Table1 WHERE Field2 IN (1, 2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[canadatct]

Thanks for the help so far. Much appreciated.

I'm basically trying to retrieve all records that have either a value of "1" and a value of "2" for Field2.

I think I was missing something from that query, so the logic probably doesn't make much sense (which I realize now). I have been working on a much more complex query, and I'm running into the same problem, so I made that simple query to help myself try and figure this out. Obviously with no results yet.

Here is my actual SQL Statement I'm working with:

Select distinct s.Employ_No, s.Full_Name, s.First_Name, s.Last_Name, s.Group_No 
	FROM (SafeData s INNER JOIN Safety_Training st ON s.Employ_No = st.Employ_No)

	WHERE st.TrainID = 1 AND st.TrainID = 2
	ORDER BY s.Full_Name asc

With this query, I'm trying to retrieve the rows in SafeData (which is a users table, joined in this query to Safety_Training), who have taken both training classes (st.TrainID=1 and st.TrainID=2), .

I have tried the IN keyword, but its not producing the correct results. I need the results for all people who have taken BOTH, not one or the other.

Thanks again for the help.

 

[PHV]

Perhaps something like this (provided a training is taken once) ?
SELECT s.Employ_No, s.Full_Name, s.First_Name, s.Last_Name, s.Group_No
FROM SafeData s
INNER JOIN Safety_Training st ON s.Employ_No = st.Employ_No
WHERE st.TrainID IN (1, 2)
GROUP BY s.Employ_No, s.Full_Name, s.First_Name, s.Last_Name, s.Group_No
HAVING COUNT(*) = 2
ORDER BY s.Full_Name ASC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[canadatct]

I think that will work great. I'm going to run some testing on the query to ensure that it's generating the correct results, but so far, it seems to be bang on.

The query will be dynamically generated, therefore if someone was selecting 20 different training classes, and wanted to see who was certified for all 20, then the COUNT(*) would have to equal 20 for it to return the correct results......correct? If this is correct, I'll assume so unless you say otherwise.

Thanks a lot for your help!

 

[PHV]

correct?
Yes, provided the IN operator is testing the 20 values.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886