Find The Baseball Players

Question

Post reply

Find The Baseball Players

Steve Jones - SSC Editor

SSC Guru

Points: 736183
More actions
August 16, 2001 at 12:00 am

#57529

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/findthebaseballplayers.asp

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

danielmcl Grasshopper Points: 19 More actions · Answer 1

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)

*/

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 2

Thanks for the complements.

Nice solution. I wish I'd included this one :).

Steve Jones

steve@dkranch.net