August 16, 2001 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/findthebaseballplayers.asp
October 3, 2001 at 2:51 pm
Steve,
First off (and to everyone) let me compliment this site. This is my first time here and it's very informative.
Secondly, let me appluade both of your solutions. Both are very effective and (as stated) easily understandable.
Following is a somewhat different, if not unusual, solution.
It is in fact possible to accomplish the same outcome in a single query. The "trick" is to use an aggregate function and a HAVING clause to manipulate string data instead of numeric data.
Following is my solution.
PS
I have also included the CREATE and INSERT statements for the sample data for others who may want/need it.
Thanks,
Daniel
declare @vchType varchar(25)
select @vchType = 'Baseball'
select
s.CustID
from
Sales s join Products p on s.ProdID = p.ProdID
group by
s.CustID
having
min(ProdTyp) = @vchType and max(ProdTyp) = @vchType
/*
create table Products (ProdID int,ProdName varchar(25),ProdTyp varchar(25))
insert into Products values(1,'Bat','Baseball')
insert into Products values(2,'Baseball','Baseball')
insert into Products values(3,'Football','Football')
insert into Products values(4,'Tee','Baseball')
insert into Products values(5,'Tee','Football')
insert into Products values(6,'Tee','Golf')
insert into Products values(7,'Basketball','Basketball')
insert into Products values(8,'Mask','Hockey')
create table Sales (CustID int, ProdID int, Qty int, Price money)
insert into sales values (1,1,3,4.00)
insert into sales values (2,1,1,5.00)
insert into sales values (2,2,1,99.00)
insert into sales values (3,4,1,50.00)
insert into sales values (4,1,2,3.00)
insert into sales values (4,2,1,4.00)
insert into sales values (4,3,2,4.00)
insert into sales values (4,5,3,9.99)
insert into sales values (5,1,3,4.00)
insert into sales values (5,3,3,14.00)
insert into sales values (6,5,3,24.00)
insert into sales values (7,7,1,54.00)
*/
October 5, 2001 at 1:57 pm
Thanks for the complements.
Nice solution. I wish I'd included this one :).
Steve Jones
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply