help on SELECT

Help on creating correct select query on the following table where customer = multi-race

( that is, customers that have value ‘1’ on more than one race category)

Thanks!

CustomerID

Black

AmIndian

Asian

White

PacIslander

Hispanic

NoRaceDisc

32501

1

1

32677

1

35062

1

1

35261

1

36490

1

41026

1

41412

1

42488

1

1

1

45471

1

47083

1

1

50066

1

Okay, first off, you should probably change your table structure if you get the chance. The way you designed things, you actually have to go through a schema change if you ever want to add a new race. I suggest you go to a table structure that has a table for race types, and another table which contains your customer_id and the race_id. In this case, your query would look something like this:

select customer_id, count(*)
from customer_races
group by customer_id
having count(*) > 1

In your current table structure, it gets a lot more complicated. If you cannot change the table structure, I'd suggest you follow a method similar to the one I outlined above. Create a temp table / table variable with the following structure...

declare table @.customer_race_count (
customer_id int, race_count int)

Then, you'll have to construct a series of statements like this...

insert into @.customer_race_count (customer_id, race_count)
select customer_id, 1 AS race_count
from customer
where black is not null

insert into @.customer_race_count (customer_id, race_count)
select customer_id, 1 AS race_count
from customer
where white is not null

then do something similar to this...

select customer_id, count(*)
from race_count
group by customer_id
having count(*) > 1

I hope that helps!

|||oh yeah, if you're using sql 2005, you might be able to take advantage of pivot / unpivot, but I've been working all day and no longer have the brain power to conjure some sample code for that.|||

well, if the race category is a numerical value (ie int) of some sort, this would work:

select CustomerID from MyTable
where (coalesce(Black,0) + coalesce(AmIndian,0) + coalesce(Asian,0) + coalesce(White,0) + (PacIslander,0) + (Hispanic,0)) > 1

If this is 2005, you may want to look at PIVOT

|||

First off, I agree completely with the person who said change the table structure. This should be a very easy query, but it isn't like you have it.

Second, if not numbers, or values aren't actually null, just change to something like

case when Black = '1' then 1 else 0 end +
case when AmIndian = '1' then 1 else 0 end + ...

and you can handle any datatype

|||

Thanks to all that responded.
I did change the structure. Created RaceTypeID 1(White),2(Black),3(Asian),4(AmIndian),and 5(Hispanic). a customer can supply more than 1 race type id so in the race table there can be multiple instances of rows with the same customerid but different race type id.

now i run a query as follows:

SELECT DISTINCT
dbo.Customers.CustomerID
,CASE WHEN EXISTS
(SELECT DISTINCT
dbo.Customers.CustomerID,
COUNT(*)
FROM dbo.Customers
INNER JOIN dbo.Race ON dbo.Customers.CustomerID = dbo.Race.CustomerID
GROUP BY dbo.Customers.CustomerID
HAVING COUNT (*) > 1) THEN 'yes' ELSE 'no' END as MultiRace
FROM dbo.Customers
WHERE
dbo.Customers.LastName NOT LIKE 'test'
AND dbo.Customers.LastName NOT LIKE 'training%'

in my result set i am getting 'yes' on all customers though only 7 are actually muti-race. Help please?

|||

Try this...

select
Customers.CustomerId,
CASE WHEN d_Race.CustomerID IS NOT NULL THEN 'YES'
ELSE 'NO'
END As MultiRace
FROM Customers
LEFT OUTER JOIN
(select customerid, count(*)
from race
group by customerid
having count(*) > 1) AS d_Race
ON d_Race.Customer_Id = Customers.Customer_Id

|||thank you very much...i only had to assign a column name for the count (*) and i finally was able to get the result i wanted. thanks a lot for all your help and to others who pitched in as well.|||whoops, yeah, you're right. I didn't alias that column. That's what happens when you develop pseudo-code. :) Glad I was able to steer you in the right direction.