I'm sure it something simple but it's driving me nuts :-( 
Author Message
 I'm sure it something simple but it's driving me nuts :-(

Hi there everybody,
I need some help with what I sure is select statement. I need to test if
both values from one Select are present in another Select statement.

Example

Select ProductID from VSale returns 9 and 22
And
Select ProductID from BasketItems returns 9

So I'm working along the lines of :-
Select SaleDetailsID from VSale where Productid in(
Select Productid from BasketItems BItem inner join
SaleDetails SD on BItem.InSale = SD.SaleDetailsID
where BasketID = 60 and (GetDate() >= StartDate and GetDate() <= EndDate))

This shouldn't return any SaleDetailsID but it does :-(

Can any body give me a different approach to this problem

Thanks in advance



Tue, 01 Feb 2005 04:29:56 GMT
 I'm sure it something simple but it's driving me nuts :-(

Lloyd,

The best way to request assistance with SQL is to post (simplified) table
schemas (CREATE TABLEs) and sample data (INSERTs).

Narratives tend to be ambiguous. For example, you indicate the data in two
of the three tables you have referenced in your query, but have neglected to
indicate the data in the third table, which implies that the third table is
irrelevant to the query.

Making a number of wild guesses, perhaps the following is close to what you
want:

create table VSale (ProductID int NOT NULL primary key)
create table BasketItems (ProductID int NOT NULL primary key)
go

insert into VSale values (9)
insert into VSale values (22)

insert into BasketItems values (9)
go

create view Lloyd
as
select b.ProductID
from VSale as v
left outer join BasketItems as b
  on (v.ProductID = b.ProductID)
go

select ProductID
from Lloyd
where not exists (select *
                         from Lloyd
                         where ProductID is null)

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> Hi there everybody,
> I need some help with what I sure is select statement. I need to test if
> both values from one Select are present in another Select statement.

> Example

> Select ProductID from VSale returns 9 and 22
> And
> Select ProductID from BasketItems returns 9

> So I'm working along the lines of :-
> Select SaleDetailsID from VSale where Productid in(
> Select Productid from BasketItems BItem inner join
> SaleDetails SD on BItem.InSale = SD.SaleDetailsID
> where BasketID = 60 and (GetDate() >= StartDate and GetDate() <= EndDate))

> This shouldn't return any SaleDetailsID but it does :-(

> Can any body give me a different approach to this problem

> Thanks in advance



Tue, 01 Feb 2005 07:52:22 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. ODBC's driving me nuts!!!

2. Simple error-I'm sure

3. A Simple Question I'm sure

4. PL/SQL Calculated Fields - Simple I'm sure

5. VERY SIMPLE DATABASE QUESTION YET DROVE ME NUTS

6. Here's an easy one, I'm sure (from a new SQL 6.5 user)

7. Something amiss with 'msnews.microsoft.com'???

8. something like 'sequence'?

9. Pick's Website - something's missing!

10. Error '80040e51' Driving Me Nuts!

11. EM doesn't see drive until it's formatted

12. Error Reading Drive 'X'


 
Powered by phpBB® Forum Software