Intersect Query 1

[skiflyer]

Is there a way to write intersect queries in SQL '99?

Or perhaps I'm just missing something entirely... I have two tables, related by 1 to many. Products, and states for those products. I want all products which were at some time in both state A and state B.... easy to enough to get all products in either state, but to do both I'd like an intersection.

Do I need to do this in my code, or can I do it in a query?

Thanks,
Rob

 

[skiflyer]

Sorry, I shouldn't have said SQL '99... I know how to do it with sub queries, but I'd like to avoid them if possible.

-Rob

 

[swampBoogie]

In SQL 99

select product
  from products join states using(productid)
 where productState in ('A','B')
 group by product
 having count(distinct productState = 2)

Shouldn't use state as an objectname, it is a reserved word in SQL 99.

 

[skiflyer]

I have a problem, when I added other columns to the select portion I get the error...

You tried to execute a query that does not include the specified expression 'Other_Column' as part of an aggregate function.

I'm confused, I thought I just had to have the aggregate in the SELECT, not that I had to have all the Selects in the aggregate...

 

[skiflyer]

Oh nevermind it turns out I can't use that type of query anyway... I might be able to adapt it, but a product can be put into state A or B several times each... hence a product whose lifecycle is

A
C
A

would be a false positive and a product whose lifecycle is
A
A
B

would be a false negative. Looks like I'm going to have to go the subquery route.

-Rob