Re: Need help to modify my query
From: Steve Kass (skass_at_drew.edu)
Date: 12/07/04
- Next message: Steve Kass: "Re: how to ban updates"
- Previous message: Mykola Rabchevskiy: "how to ban updates"
- In reply to: mitra: "Re: Need help to modify my query"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 06 Dec 2004 22:41:04 -0500
Mitra,
This is not very well-suited to SQL, because the rows in
the result set don't represent any real-world entity. The
e-mail address and the group name that appear in a particular
row both belong to the same person, but they are not
otherwise related to each other. A report writer might
do a much better job here.
Here's a query that will produce this report - it works
by creating an artificial relationship between email and
grou names by considering them related if the ordinal
position of the email address among all email addresses
for the given person equals the ordinal position of the
group id among all the groups for the given person, and
then uses an outer join, since there may be different numbers
of each for a given person. The query then only prints
the user name if the ordinal position of the email or group
name is 1.
select
case when coalesce(emailRank,gnameRank) = 1
then coalesce(euname, guname)
else '' end as name,
coalesce(email,'') as email,
coalesce(groupname,'') as groupName
from (
select
E.euname, E.email, E.emailRank,
G.guname, G.groupname, G.gnameRank
from (
select
su.id_user,
su.name as euname,
sue1.email,
(select count(*) from S_useremail sue2
where sue2.id_user = su.id_user
and sue2.email >= sue1.email
) as emailRank
from S_user as su
left outer join S_useremail as sue1
on su.id_user = sue1.id_user
) E full outer join (
select
su.id_user,
su.name as guname,
sg1.name as groupname,
(select count(*) from S_group sg2
where sg2.id_group in (
select sug.id_group from S_usergroup sug
where sug.id_user = su.id_user
) and sg2.id_group <= sg1.id_group
) as gnameRank
from S_user as su
left outer join S_usergroup as sug
on sug.id_user = su.id_user
left outer join S_group as sg1
on sg1.id_group = sug.id_group
) G
on E.id_user = G.id_user
and emailRank = gnameRank
) T
order by
coalesce(euname, guname),
coalesce(emailRank,gnameRank)
-- Steve Kass
-- Drew University
-- Ref: 66C57956-71B7-432F-AD7C-11568CB8747F
mitra wrote:
>Hugo,
>
>Sorry , I was not clear with how I need the query result.
>Below is the ddl and the data your rquested and with
>more explanation of how I need the query to return the result.
>============================================
>CREATE TALBE S_User (
> id_user int not null,
> name varchar (30)
>)
>
>INSERT INTO S_user ( id_user, name)
>VALUES (1, 'mitra')
>INSERT INTO S_user ( id_user, name)
>VALUES (2, 'john')
>INSERT INTO S_user ( id_user, name)
>VALUES (3, 'patrick')
>
>
>CREATE TABLE S_Group (
> id_group int not null,
> name varchar(30)
>)
>
>INSERT INTO S_group ( id_group, name)
>VALUES (1, 'Sales')
>INSERT INTO S_group ( id_group, name)
>VALUES (2, 'Operation')
>INSERT INTO S_group ( id_group, name)
>VALUES (3, 'Human Resources')
>INSERT INTO S_group ( id_group, name)
>VALUES (4, 'Technical Support)
>
>CREATE TABLE S_useremail (
> id_user int not null,
> email Varchar(30)
>)
>
>INSERT INTO S_useremail (id_user, email)
>Values(1, 'mitra@abc.com')
>INSERT INTO S_useremail (id_user, email)
>Values(1, 'mitra@att.com')
>INSERT INTO S_useremail (id_user, email)
>Values(2,'john@abc.com')
>INSERT INTO S_useremail (id_user, email)
>Values(2, 'john@att.com')
>INSERT INTO S_useremail (id_user, email)
>Values(3,'patrick@abc.com')
>INSERT INTO S_useremail (id_user, email)
>Values(3, 'patrick@att.com')
>
>CREATE TABLE S_userGroup (
> id_user int,
> id_group int
>)
>
>INSERT INTO S_userGroup (id_user, id_group)
>VALUES (1,1)
>INSERT INTO S_userGroup (id_user, id_group)
>VALUES (1,2)
>INSERT INTO S_userGroup (id_user, id_group)
>VALUES (1,3)
>INSERT INTO S_userGroup (id_user, id_group)
>VALUES (2,1)
>INSERT INTO S_userGroup (id_user, id_group)
>VALUES (2,2)
>INSERT INTO S_userGroup (id_user, id_group)
>VALUES (2,3)
>INSERT INTO S_userGroup (id_user, id_group)
>VALUES (3,2)
>INSERT INTO S_userGroup (id_user, id_group)
>VALUES (3,3)
>
>Please note users can belong to more than one group and users
>can have more than one email address.
>
>I like to avoid repeating the name and user's email
>addresses for every gorup to which they belong.
>
>I like to change the query to return a result like:
>===========================================
>NAME EMAIL_ADDRESS Group_NAME
>
>mitra mitra@abc.com, Sales
> mitra@att.com Human Resource
> Operation
>
>john john@abc.com, Sales
> john@att.com Human Resources
> Operation
>
>Patrick patrick@abc.com, Human Resources
> patrick@att.com Operation
>============================================
>
>Below is the query statement that i need help to modify
>to make it return results like above:
>
>==============================================
>SELECT [User Name],
> [Email Address],
> [Group Name]
>FROM (SELECT
>su.fullname as RealUserName,
> sue.email as RealEmailAdd,
>'' as [User Name],
>'' as [Email Address],
>sg.name as [Group Name]
>FROM S_userGroup sug
>LEFT JOIN S_user su ON sug.id_sysUser = su.id
>LEFT JOIN S_group sg ON sug.id_group = sg.id
>LEFT JOIN S_useremail sue ON su.id = sue.id_user
>WHERE sg.name <> (
> SELECT MIN (sg2.name)
> FROM S_userGroup sug2
> LEFT JOIN S_group sg2
> ON sug2.id_user = sug.id_user
> AND sug2.id_group = sg2.id
> )
>
>UNION ALL
>SELECT
> su.fullname,
>sue.email,
>su.fullname,
>sue.email,
> min(sg.name)
>FROM S_userGroup sug
>LEFT JOIN S_user su ON sug.id_user = su.id
>LEFT JOIN S_group sg ON sug.id_group = sg.id
>LEFT JOIN S_useremail sue ON su.id = sue.id_user
>GROUP BY su.fullname,sue.email )
> Report
>ORDER BY RealUserName, RealEmailAdd, [Group Name]
>
>==================================================
>
>Thank you so much!
>
>--Mitra
>
>
>
>"Hugo Kornelis" wrote:
>
>
>
>>On Fri, 3 Dec 2004 12:23:01 -0800, mitra wrote:
>>
>>
>>
>>>Hi,
>>>
>>>After learning how to do the query statement shown below, of course with
>>>help from this newsgroup, I now need to modify the statement to return all
>>>the email addresses once as well. I tried but I could not get it to work.
>>>
>>>Query below returns results like:
>>>===============================================
>>>Mitra Brown mbn@abc.com Human Resources
>>> Operations
>>> Technical Support
>>>Mitra Brown mbn@att.com Human Resources
>>> Operations
>>> Technical Support
>>>
>>>
>>>================================================
>>>
>>>I like to modify the query to return a result like:
>>>
>>>Mitra Brown mbn@abc.com Human Resources
>>> mbn@att.com Operations
>>> Technical Support
>>>
>>>
>>(snip)
>>
>>Hi mitra,
>>
>>I assume that you have already been advised to do the presentation logic
>>in the presentation tier?
>>
>>Like any other presentation stuff, it CAN be done in SQL but it will be a
>>bitch to write and an even nastier bitch to maintain. Plus, it will
>>probably perform like a snail in fresh tar.
>>
>>Before I can get to the nitty gritty of the query, I need to know *how*
>>SQL Server should decide to pair the abc-address with Human Resources and
>>the att-address with Operations. What is the logical relation that causes
>>these pairs (and not other possible pairs) to be formed?
>>
>>Finally: please post CREATE TABLE and INSERT statements that allow me to
>>quickly reproduce your tables and sample data, so that I can use that for
>>testing. If you already posted this before, provide a message-ID or a
>>Google link (with several hundred messages daily in this group, it's hard
>>to find anything if oyu don't know exactly where to look).
>>
>>Best, Hugo
>>--
>>
>>(Remove _NO_ and _SPAM_ to get my e-mail address)
>>
>>
>>
- Next message: Steve Kass: "Re: how to ban updates"
- Previous message: Mykola Rabchevskiy: "how to ban updates"
- In reply to: mitra: "Re: Need help to modify my query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
