x=1 AND x=2 sounds right, but not if you think about it in terms of the x value of each row
what you want is the existence of two rows, one of each
left join sales
on stores.stor_id = sales.stor_id
where 2 =
( select count(*)
where store.id = stores.stor_id
and ( title_id = 'PS2091'
or title_id = 'BU1032' )
the subselect is correlated, looking at all rows for each store, counting the ones that meet any of the individual criteria -- if this count is 2, then that store qualifies, and all of its sales rows are selected in the outer query
i'm not familiar with the pubs database and am assuming that there's at most one sales row per title per store, otherwise the query won't work right
You are right - my original AND query did run and return no results in SQL Server but did return the correct results in MS Access (exactly the same query).
I implemented the new code (that you provided) and this works perfectly and returns the same set of results in SQL Server that is returned in Access with my original AND query.
The actual query in my application is loads more complicated as it has six join tables and also queries criteria in the master table as it well. It also has nothing to do with sales or stores either - just used this as an example of the problem. Ultimately - It is a very very large SQL statement that is generated in the end. I just cut it down to what I posted initially on the forum to simplify it for the post, as I knew this was the bit that wasn't working (six times over).
I know exactly what your saying that the query is 'impossible' as it is asking one value to be different values, which is completely not possible which is why it returned no results.
I orginally developed the application that is using this is VB6 + Access and then transposed it to an SQL Server back-end which is where the problem arose. But all working now thanks to you.