Please help with Simple Query Design

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

SQL Server Development (2000)

Please help with Simple Query Design

Author

Topic

Petehe
Starting Member

20 Posts

Posted - 2003-05-30 : 00:56:57

I have two tables
Table A,
CustomerID CustomerName
1 Sony
2 LG

Table B,
CustomerID CustomerUserName
1 Alan
1 Pete
1 Mike
2 Simon
2 Mattew

It is a simple one to many relationship.
Can anyone help me to design one query to show something like below

CustomerName CustomerUserNameCollection
Sony Alan,Pete,Mike
LG Simon, Mattew

Thanks

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-30 : 01:16:33

I know this isn't exactly what you asked for, but it gives you the same info, and it is to late for me to think about how to get the usernames in a comma delimited list.


select CustomerName , CustomerUserName 
from TableA a
join TableB b on a.CustomerID=b.CustomerID
order by CustomerName

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

peddi_praveen
Starting Member

48 Posts

Posted - 2003-05-30 : 02:51:54

hi pete,
here is the way to go

---------------
for a given custid
---------------

declare @usr_name varchar(100)

select @usr_name= coalesce( @Usr_Name + ',', '') + usr_name
from cust a
join cust_user b
on a.Cust_ID=b.Cust_ID
where a.cust_id= 1 -- @input

select @usr_name

---------------------------------------------
for all the recs in table
------------------------------------------

declare @usr_name_csv varchar(100)
declare @cust_id int

create table #one_many
(cust_id int,
names varchar (100)
)

INSERT INTO #one_many
SELECT cust_id , NULL
from cust

declare cur_cust cursor
FOR select cust_id
from #one_many
open cur_cust

fetch next from cur_cust
into @cust_id
while (@@fetch_status =0)
begin

select @usr_name_csv= coalesce( @Usr_Name_csv + ',', '') + usr_name
from cust a
join cust_user b
on a.Cust_ID=b.Cust_ID
where a.cust_id= @cust_id -- @input

update #one_many
set names = @usr_name_csv
where cust_id = @cust_id

-- clear the variable
set @usr_name_csv= null

fetch next from cur_cust
into @cust_id
end
close cur_cust
deallocate cur_cust

select cust.cust_id,
cust_name,
names as user_names
from #one_many tmp
join cust
on tmp.cust_id = cust.cust_id

drop table #one_many

----------------------

hope,this helps u...
btw, if u get any other solution,without using temp tables,cursors, post it here

PS: if the user names table is too large, consider the performance issues

praveen

cas_o
Posting Yak Master

154 Posts

Posted - 2003-05-30 : 04:31:39

This will do the same without a cursor, I have adapted some T-SQL that has been invaluable to me from one of Graz's articles on delimited lists. Enjoy

/*Create a temp table to hold the usernames in customer order this is important for the 'non-cursor' update statement to work all names from each company need to be together in the table*/

CREATE TABLE #table_B
(
CustomerID int,
CustomerUserName varchar(35),
CustomerList varchar(100)
)
Insert Into #table_B (CustomerID,CustomerUserName)
Select CustomerID,CustomerUserName
from table_B
Order by CustomerID,CustomerUserName

/*This is the clever bit*/

DECLARE @List varchar(100), @LastCust int
SELECT @List = '',@LastCust = 0

UPDATE
#table_B
SET
@List = CustomerList = CASE
WHEN @LastCust <> CustomerID THEN rtrim(CustomerUserName)
ELSE @List + ', ' + rtrim(CustomerUserName)
END,
@LastCust = CustomerID

/* This gets you the result you want */
Select
Sublist.CustomerName,#table_B.CustomerList
from
table_A,#table_B,
(Select table_A.CustomerID,table_A.CustomerName,max(len(#table_B.CustomerList)) Longest
from table_A join #table_B on table_A.customerID=#table_B.CustomerID
group by table_A.CustomerID,table_A.CustomerName) sublist
Where
table_A.CustomerID = Sublist.CustomerID
and Sublist.Longest=len(#table_B.CustomerList)

;-]

peddi_praveen
Starting Member

48 Posts

Posted - 2003-05-30 : 06:31:34

hi,

better way of doing it.

vl u plz explain the following update stmt.
i got wat it does, i dnt get how it does

UPDATE
#table_B
SET
@List = CustomerList = CASE
WHEN @LastCust <> CustomerID THEN rtrim(CustomerUserName)
ELSE @List + ',' + rtrim(CustomerUserName)
END,
@LastCust = CustomerID

cas_o
Posting Yak Master

154 Posts

Posted - 2003-05-30 : 06:57:39

If you look here:

http://www.sqlteam.com/item.asp?ItemID=11021

that's the begining, at the bottom of the article is a link to a series of posts where how and why it works is discussed.

For me it's just one of those T-SQL multidextrous things that I don't think would port to any other RDBMS !! Although it's a set based method there is iteration going on, there has to be because the variables are being set for each row.

I love the CASE statement, but watch out it bites if you don't cover ALL posibilities. there is an ELSE keyword for this which I havn't needed in this example.

;-]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources