select query - 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)

select query

Author

Topic

niravg
Starting Member

17 Posts

Posted - 2004-12-22 : 16:22:38

hi all

i have three tables

ACT(actid,actname) - account
ACT-CON(actid,conid)
CON(conid, conname) - contact

CON and ACT has one-many relation

means one contacts can have more than one accounts

my question is

i want all the conname and corrosponding actname , but if conid is connnected with more than on actid then instead actname it should return "multiple"

how do i do this

any help

thanks

-nii

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-22 : 16:28:19

select a.actname, case when count(*) > 1 then 'multiple' else max(c.conname) end
from act a
join [act-con] ac
on a.actid = ac.actid
join con c
on c.conid = ac.conid
group by a.actname

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

niravg
Starting Member

17 Posts

Posted - 2004-12-22 : 16:35:17

buddy

i want conname and corrosponding actname, if conid has more than one actid then it should display multiple

can u plz. modify ur script

thanks

-nii

jhermiz

3564 Posts

Posted - 2004-12-22 : 16:41:19

o come on he gave you the answer already, you can plug in your own fields.


select a.actname, c.conname, case when count(*) > 1 then 'multiple' else max(c.conname) end 
from act a
join [act-con] ac
on a.actid = ac.actid
join con c
on c.conid = ac.conid
group by a.actname

A new beat on the web -- http://www.web-impulse.com

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-22 : 18:10:59

There was an elderly grandmother who was walking with her only grandson, age 4, along a deserted beachfront. Without warning a huge wave came onshore and swept the small boy away - far out into dangerous waters. The woman, being too frail to to attempt the roiling waters did the only thing she could. She dropped to her knees, with tears in her eyes, she lifted to voice to the heavens and proclaimed, "Lord above us, I stand here a humble supplicant before you. I am an old woman who has always tried to abide in your ways. I have nothing, Lord, to bring me joy in my old age except for my only grandson. I have never asked for anything from you before but please hear my most pitiful plea. Spare the life of this boy and I shall be eternally grateful."

With that, a second wave, just as big as the first, swept the helpless boy back to shore and placed him at the foot of the praying woman. None the worse for wear, the small lad jumped up and was immediately embraced by the woman where she clutched him tightly just to fell his still beating heart.

When they parted their embrace, she looked closely at the boy, turned her eyes back to the heavens and said, "Lord....He had a hat."

And our vocabulary word for the day is: Gratitude.

HTH

=================================================================

Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -Mark Twain, author and humorist (1835-1910)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-22 : 18:14:33

Due to the GROUP BY, you can't just plug in your own column names. But we can certainly help you fix the query if you showed us an example with data of what you want.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources