multiple table values - SQL Server Forums

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)

multiple table values

Author

Topic

blast
Starting Member

9 Posts

Posted - 2006-12-12 : 21:16:56

hi,

i need ur help....please help
i have 3 tables , when i try to bring an ticket filed in call table,
and chk with act table for related issues belonging to that ticket and bring all the issues filed for the ticket, meanwhile the persons filed the ticket, person reported to and the assignee are stored in their id in the call and act table, the related names is found in contact table...........now the below query gives two entries of each issue of the same ticket with their names, i wanted to be displayed in single row without duplication to be displayed in separate field

----say for eg :contact.firstname "login person" , contact.firstname "reported person"
not sure how to do it.......please im desperate help me

select ca.first_name, ca.last_name from contact ca, call c, act a
where ((ca.contact_id = c.assi) or (ca.contact_id = a.analy)or (ca.contact_id = c.log_ag))
and c.ref = '00322' and c.perid = a.cid

thanks in advance

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-12 : 23:21:15

How you wanted the output to look like? This seems to be candidate for Cross-tab query.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

blast
Starting Member

9 Posts

Posted - 2006-12-13 : 08:57:42

hi,
thanks for ur efforts.....

the original ouptput which i get for this query is
firstname lastname
jery ben
san jose
jery ben
san jose
jery ben
san jose

I wanted the ouptput something like this for each issue belonging to the ticket I wanted

Log“fir+lasnam” assi“fir+lasname” analyst “fir+lastname”
Jey ben san jose san jose

please help me im not sure...how to get this, thanks a lot

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-13 : 09:18:19

may be this?

select 
	case
		when c.log_ag is not null then ca.first_name + ' ' + ca.last_name
	end as [Log],
	case
		when c.assi is not null then ca.first_name + ' ' + ca.last_name
	end as Assi,
	case
		when a.analy is not null then ca.first_name + ' ' + ca.last_name
	end as Analyst
from
	contact ca
	left join call c on (ca.contact_id = c.assi or ca.contact_id = c.log_ag)
	left join act a on ca.contact_id = a.analy
where
	c.ref = '00322' and 
	c.perid = a.cid

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources