Query help, DDL an DML provided

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query help, DDL an DML provided

Author

Topic

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-03-18 : 09:21:07

I am looking for a best possible solution for the following:

The 3rd and 5th rows is what I want to eliminate but don't know how.

22 Honda Sara Ken sk22@xyz.com
22 Honda smith Kline sk22@xyz.com
22 Honda x x x
44 Honda x x x
55 Honda Cindy Drexel cd22@xyz.com
55 Honda x x x
66 Honda Joe Louis jl22@xyz.com
66 Mazda x x x
77 Honda x x x
88 Honda x x x

CREATE TABLE a (
RId int NOT NULL ,
Customer varchar(50),
Focus varchar(50),
Fname varchar(50),
Lname varchar(50),
Email varchar(50),
CustId int
)

CREATE TABLE b (
CustId int NOT NULL ,
Region varchar(50),
Location varchar(50)

)

Insert into a values(1,'Honda','Service','smith','Kline','sk22@xyz.com,22)
Insert into a values(2,'Honda','Parts','Bob','Brando','bb22@xyz.com,44)

Insert into a values(3,'Honda','Service','Cindy','Drexel','cd22@xyz.com,55)

Insert into a values(4,'Honda','Service','Joe','Louis','jl22@xyz.com,66)

Insert into a values(5,'Honda','Service','Kerry','John','kj22@xyz.com,77)

Insert into a values(6,'Honda','Service','Bill','Smith','bs22@xyz.com,88)

Insert into a values(7,'Honda','Service','Tom','Herder','th22@xyz.com,55)

Insert into a values(8,'Honda','Service','Miller','Campbell','mc22@xyz.com, 22)

Insert into a values(9,'Mazda','Service','Lori','Bell','lb22@xyz.com,66)

Insert into a values(10,'Honda','Service','Sara','Ken','sk22@xyz.com',22)

Insert into b values(22,'Somereg1','someloc1')
Insert into b values(44,'Somereg2','someloc2')
Insert into b values(55,'Somereg3','someloc3')
Insert into b values(66,'Somereg4','someloc4')
Insert into b values(77,'Somereg5','someloc5')
Insert into b values(77,'Somereg6','someloc6')
Insert into b values(88,'Somereg7','someloc7')
Insert into b values(11,'Somereg8','someloc8')

Select id,customer,
case when (rid in (1,3,4)) then Fname as CustFirst,
case when (rid in (1,3,4)) then lname as CustLast,
case when (rid in (1,3,4)) then email as Custemail
from a
join b on a.custid=b.custid
order by a.custid

This may be a totally wron way of writing the query.
I want all dealers to show up and only for those who have a rank( id in 1,3....) I want CustFirst,.... columns to show up.
Since Honda(22,55) dealer for example, is already showing up. So I do not want them to show up with null values.
These tables are with millions of records and hence I have to keep in mind the time and performance.

Any help is hugely appreciated.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-18 : 13:24:42

Thanks for the DDL and DML....

But I don't see what rows 3 and 5 have anything that I can target..

It's not even a row offset question....

What makes them special?

How do you know they need to be eliminated from the rs?

Brett

8-)

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-03-18 : 13:30:48

i want all dealers to show up and only for those who have a rank( id in 1,3....) I want CustFirst,CustLast,CustEmail columns to show up.
Since Honda(22,55) dealerS for example, are already showing up. So I do not want them to show up again with null values.

for example:
22 Honda Sara Ken sk22@xyz.com
22 Honda smith Kline sk22@xyz.com
22 Honda x x x

I already have Honda dealer who has 2 contacts, I do not want the third row.
I hope I am making sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources