May 18, 2011 at 7:26 am
Lynn Pettis (5/18/2011)
I know I'm late to the party, but I was reading the question and I'm partially lost, in that I'm not sure what your expected results are based on the sample data. Are you looking for all sales where a soda and a hamburger were purchased or just a soda and a hamburger?
Agreed. Would you want an order that had soda, hamburger, and fries, or just ones that are an item-by-item total match?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 18, 2011 at 7:48 am
Lynn Pettis (5/18/2011)
I know I'm late to the party, but I was reading the question and I'm partially lost, in that I'm not sure what your expected results are based on the sample data. Are you looking for all sales where a soda and a hamburger were purchased or just a soda and a hamburger?
Right! Expected results. I knew I forgot something.
You'd want any order in which a soda and a hamburger were ordered; if they just ordered that, or if they ordered that and a dozen other things too.
Lets say they ask for Products 100 and 200, the results would be
SaleID
1
2
3
4
If they ask for Products 200, 370, 500, the results would be
SaleID
2
3
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 18, 2011 at 7:49 am
ColdCoffee (5/18/2011)
Lynn Pettis (5/18/2011)
I know I'm late to the party, but I was reading the question and I'm partially lost, in that I'm not sure what your expected results are based on the sample data. Are you looking for all sales where a soda and a hamburger were purchased or just a soda and a hamburger?+ 1 ... Need "Expected Results" to do any coding 🙂 I think i have a way to do this 🙂
Great, I look forward to seeing it. Sorry I forgot the expected results.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 18, 2011 at 7:49 am
Greg Edwards-268690 (5/18/2011)
Seems more like a data mining model than a query.Why is it that they want to see this?
Just a thought....
Partly data mining, partly helping to hunt down problems.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 18, 2011 at 7:51 am
GSquared (5/18/2011)
Lynn Pettis (5/18/2011)
I know I'm late to the party, but I was reading the question and I'm partially lost, in that I'm not sure what your expected results are based on the sample data. Are you looking for all sales where a soda and a hamburger were purchased or just a soda and a hamburger?Agreed. Would you want an order that had soda, hamburger, and fries, or just ones that are an item-by-item total match?
You'd want any order in which a soda and a hamburger were ordered. If they just ordered that, or if they ordered that and a dozen other things too.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 18, 2011 at 9:25 am
Stefan Krzywicki (5/18/2011)
GSquared (5/18/2011)
Lynn Pettis (5/18/2011)
I know I'm late to the party, but I was reading the question and I'm partially lost, in that I'm not sure what your expected results are based on the sample data. Are you looking for all sales where a soda and a hamburger were purchased or just a soda and a hamburger?Agreed. Would you want an order that had soda, hamburger, and fries, or just ones that are an item-by-item total match?
You'd want any order in which a soda and a hamburger were ordered. If they just ordered that, or if they ordered that and a dozen other things too.
In that case, a rowcount on the criteria and a rowcount on a join from the criteria to the data should give you what you need, so long as you force the right "distinct" operators on it. That's how I've usually done it.
Another way is to use the INTERSECT operator for each criterion, but that can get unwieldy to code without going recursive or procedural on it.
select ID
from MyTable
where MyColumn = @CriterionOne
INTERSECT
select ID
from MyTable
where MyColumn = @CriterionTwo or @CriterionTwo is null
INTERSECT
select ID
from MyTable
where MyColumn = @CriterionThree or @CriterionThree is null;
That kind of thing. With a finite number of criteria, that can end up with a pretty decent execution plan, but it can also end up being a mess, and it doesn't scale well to a lot of criteria. Best done as part of a catch-all dynamic SQL solution.
But really, I just use the comparisons of row counts, and treat other solutions as pretty much academic exercises. In the realms of "can it be done" more than "should it be done".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 18, 2011 at 10:07 am
I did the following using Oracle (yes, that's what I am working with right now) but you should be able to adapt the query for use in SQL Server:
with salesprods as (
select
sd.salesid,
sd.prodid,
count(sd.prodid) over (partition by sd.salesid) as prodcnt
from
salesdetail sd
inner join prodlist pl
on (sd.prodid = pl.prodid)
group by
sd.salesid, sd.prodid
)
select distinct sp.salesid from salesprods sp
where sp.prodcnt = (select count(*) from prodlist)
order by
sp.salesid;
May 18, 2011 at 10:09 am
Lynn Pettis (5/18/2011)
I did the following using Oracle (yes, that's what I am working with right now) but you should be able to adapt the query for use in SQL Server:
with salesprods as (
select
sd.salesid,
sd.prodid,
count(sd.prodid) over (partition by sd.salesid) as prodcnt
from
salesdetail sd
inner join prodlist pl
on (sd.prodid = pl.prodid)
group by
sd.salesid, sd.prodid
)
select distinct sp.salesid from salesprods sp
where sp.prodcnt = (select count(*) from prodlist)
order by
sp.salesid;
Thanks, looks interesting. I'll give it a go as soon as I have time again.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply