Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Which join?

9 posts in DataWindow Last posting was on 2009-10-28 16:25:33.0Z
Jason 'Bug' Fenter [TeamSybase] Posted on 2009-10-21 19:38:23.0Z
From: "Jason 'Bug' Fenter [TeamSybase]" <jason.fenter@teamsybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Which join?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4adf632f$1@forums-1-dub>
Date: 21 Oct 2009 12:38:23 -0700
X-Trace: forums-1-dub 1256153903 10.22.241.152 (21 Oct 2009 12:38:23 -0700)
X-Original-Trace: 21 Oct 2009 12:38:23 -0700, vip152.sybase.com
Lines: 25
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90116
Article PK: 409732

I've done and gone ignorant.

I have a "users" table and a "roles" table. I have a "permissions" table
that manages the many-to-many relationship between users and roles. I
need a SQL statement that gives me all users and all roles with a flag
indicating the existence (or non-existence) of a user's association with
a role.

In other words, imagine user "JDoe" who is associated with roles
"Security" and "Public". Image user "SRandom" associated with roles
"Admin" and "Public". Image a role of "Sales" with no users associated
with it. I need a query that brings back this result set:

JDoe, Public, 1
JDoe, Admin, 0
JDoe, Security, 1
JDoe, Sales, 0
SRandom, Public, 1
SRandom, Admin, 1
SRandom, Security, 0
SRandom, Sales, 0


I could do some aggregation in the datawindow itself, but I *should
know* this SQL and it's really bugging me!


Ivaylo Ivanov Posted on 2009-10-21 19:52:14.0Z
From: "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4adf632f$1@forums-1-dub>
Subject: Re: Which join?
Lines: 42
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4adf666e@forums-1-dub>
Date: 21 Oct 2009 12:52:14 -0700
X-Trace: forums-1-dub 1256154734 10.22.241.152 (21 Oct 2009 12:52:14 -0700)
X-Original-Trace: 21 Oct 2009 12:52:14 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90117
Article PK: 409721

Something like this:

select users.usr_name, roles.role_name,
(select sign(count(*))
from permissions
where users.usr_id = permissions.usr_id and roles.role_id =
permissions.role_id) as existence
from users, roles
order by 1, 2

Regards,
Ivaylo

"Jason 'Bug' Fenter [TeamSybase]" <jason.fenter@teamsybase.com> wrote in
message news:4adf632f$1@forums-1-dub...
> I've done and gone ignorant.
>
> I have a "users" table and a "roles" table. I have a "permissions" table
> that manages the many-to-many relationship between users and roles. I need
> a SQL statement that gives me all users and all roles with a flag
> indicating the existence (or non-existence) of a user's association with a
> role.
>
> In other words, imagine user "JDoe" who is associated with roles
> "Security" and "Public". Image user "SRandom" associated with roles
> "Admin" and "Public". Image a role of "Sales" with no users associated
> with it. I need a query that brings back this result set:
>
> JDoe, Public, 1
> JDoe, Admin, 0
> JDoe, Security, 1
> JDoe, Sales, 0
> SRandom, Public, 1
> SRandom, Admin, 1
> SRandom, Security, 0
> SRandom, Sales, 0
>
>
> I could do some aggregation in the datawindow itself, but I *should know*
> this SQL and it's really bugging me!


Jason 'Bug' Fenter [TeamSybase] Posted on 2009-10-21 19:53:27.0Z
From: "Jason 'Bug' Fenter [TeamSybase]" <jason.fenter@teamsybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Which join?
References: <4adf632f$1@forums-1-dub> <4adf666e@forums-1-dub>
In-Reply-To: <4adf666e@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4adf66b7$1@forums-1-dub>
Date: 21 Oct 2009 12:53:27 -0700
X-Trace: forums-1-dub 1256154807 10.22.241.152 (21 Oct 2009 12:53:27 -0700)
X-Original-Trace: 21 Oct 2009 12:53:27 -0700, vip152.sybase.com
Lines: 70
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90118
Article PK: 409722

I'll have to do some performance comparisons. I came up with:
SELECT sec_Users.UserID,
sec_Roles.Descriptor,
1
FROM sec_Users,
sec_Roles
WHERE EXISTS (
SELECT 1
FROM sec_Permissions
WHERE sec_Permissions.UserID = sec_Users.UserID
AND sec_Permissions.RoleID = sec_Roles.RoleID
)
UNION
SELECT sec_Users.UserID,
sec_Roles.Descriptor,
0
FROM sec_Users,
sec_Roles
WHERE NOT EXISTS (
SELECT 1
FROM sec_Permissions
WHERE sec_Permissions.UserID = sec_Users.UserID
AND sec_Permissions.RoleID = sec_Roles.RoleID
)

Ivaylo Ivanov wrote:
> Something like this:
>
> select users.usr_name, roles.role_name,
> (select sign(count(*))
> from permissions
> where users.usr_id = permissions.usr_id and roles.role_id =
> permissions.role_id) as existence
> from users, roles
> order by 1, 2
>
> Regards,
> Ivaylo
>
> "Jason 'Bug' Fenter [TeamSybase]" <jason.fenter@teamsybase.com> wrote in
> message news:4adf632f$1@forums-1-dub...
>> I've done and gone ignorant.
>>
>> I have a "users" table and a "roles" table. I have a "permissions" table
>> that manages the many-to-many relationship between users and roles. I need
>> a SQL statement that gives me all users and all roles with a flag
>> indicating the existence (or non-existence) of a user's association with a
>> role.
>>
>> In other words, imagine user "JDoe" who is associated with roles
>> "Security" and "Public". Image user "SRandom" associated with roles
>> "Admin" and "Public". Image a role of "Sales" with no users associated
>> with it. I need a query that brings back this result set:
>>
>> JDoe, Public, 1
>> JDoe, Admin, 0
>> JDoe, Security, 1
>> JDoe, Sales, 0
>> SRandom, Public, 1
>> SRandom, Admin, 1
>> SRandom, Security, 0
>> SRandom, Sales, 0
>>
>>
>> I could do some aggregation in the datawindow itself, but I *should know*
>> this SQL and it's really bugging me!
>
>


Ivaylo Ivanov Posted on 2009-10-21 19:58:18.0Z
From: "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4adf632f$1@forums-1-dub> <4adf666e@forums-1-dub> <4adf66b7$1@forums-1-dub>
Subject: Re: Which join?
Lines: 83
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4adf67da@forums-1-dub>
Date: 21 Oct 2009 12:58:18 -0700
X-Trace: forums-1-dub 1256155098 10.22.241.152 (21 Oct 2009 12:58:18 -0700)
X-Original-Trace: 21 Oct 2009 12:58:18 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90120
Article PK: 409726

What if your first part transforms to:
SELECT DISTINCT sec_Users.UserID,
sec_Roles.Descriptor,
1
FROM sec_Users,
sec_Roles,
sec_Permissions
WHERE sec_Permissions.UserID = sec_Users.UserID
AND sec_Permissions.RoleID = sec_Roles.RoleID

"Jason 'Bug' Fenter [TeamSybase]" <jason.fenter@teamsybase.com> wrote in
message news:4adf66b7$1@forums-1-dub...
> I'll have to do some performance comparisons. I came up with:
> SELECT sec_Users.UserID,
> sec_Roles.Descriptor,
> 1
> FROM sec_Users,
> sec_Roles
> WHERE EXISTS (
> SELECT 1
> FROM sec_Permissions
> WHERE sec_Permissions.UserID = sec_Users.UserID
> AND sec_Permissions.RoleID = sec_Roles.RoleID
> )
> UNION
> SELECT sec_Users.UserID,
> sec_Roles.Descriptor,
> 0
> FROM sec_Users,
> sec_Roles
> WHERE NOT EXISTS (
> SELECT 1
> FROM sec_Permissions
> WHERE sec_Permissions.UserID = sec_Users.UserID
> AND sec_Permissions.RoleID = sec_Roles.RoleID
> )
>
>
>
> Ivaylo Ivanov wrote:
>> Something like this:
>>
>> select users.usr_name, roles.role_name,
>> (select sign(count(*))
>> from permissions
>> where users.usr_id = permissions.usr_id and roles.role_id =
>> permissions.role_id) as existence
>> from users, roles
>> order by 1, 2
>>
>> Regards,
>> Ivaylo
>>
>> "Jason 'Bug' Fenter [TeamSybase]" <jason.fenter@teamsybase.com> wrote in
>> message news:4adf632f$1@forums-1-dub...
>>> I've done and gone ignorant.
>>>
>>> I have a "users" table and a "roles" table. I have a "permissions" table
>>> that manages the many-to-many relationship between users and roles. I
>>> need a SQL statement that gives me all users and all roles with a flag
>>> indicating the existence (or non-existence) of a user's association with
>>> a role.
>>>
>>> In other words, imagine user "JDoe" who is associated with roles
>>> "Security" and "Public". Image user "SRandom" associated with roles
>>> "Admin" and "Public". Image a role of "Sales" with no users associated
>>> with it. I need a query that brings back this result set:
>>>
>>> JDoe, Public, 1
>>> JDoe, Admin, 0
>>> JDoe, Security, 1
>>> JDoe, Sales, 0
>>> SRandom, Public, 1
>>> SRandom, Admin, 1
>>> SRandom, Security, 0
>>> SRandom, Sales, 0
>>>
>>>
>>> I could do some aggregation in the datawindow itself, but I *should
>>> know* this SQL and it's really bugging me!
>>


Ivaylo Ivanov Posted on 2009-10-21 20:19:50.0Z
From: "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4adf632f$1@forums-1-dub> <4adf666e@forums-1-dub> <4adf66b7$1@forums-1-dub>
Subject: Re: Which join?
Lines: 86
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4adf6ce6@forums-1-dub>
Date: 21 Oct 2009 13:19:50 -0700
X-Trace: forums-1-dub 1256156390 10.22.241.152 (21 Oct 2009 13:19:50 -0700)
X-Original-Trace: 21 Oct 2009 13:19:50 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90122
Article PK: 409730

And another possibility for the second part:

SELECT t.UserID, sec_Roles.Descriptor, 0
FROM sec_Roles,
(SELECT sec_Users.UserID,
sec_Roles.RoleID
FROM sec_Users, sec_Roles
MINUS
SELECT DISTINCT sec_Permissions.UserID,
sec_Permissions.RoleID
FROM sec_Permissions) t
WHERE sec_Roles.RoleID = t.RoleID

"Jason 'Bug' Fenter [TeamSybase]" <jason.fenter@teamsybase.com> wrote in
message news:4adf66b7$1@forums-1-dub...
> I'll have to do some performance comparisons. I came up with:
> SELECT sec_Users.UserID,
> sec_Roles.Descriptor,
> 1
> FROM sec_Users,
> sec_Roles
> WHERE EXISTS (
> SELECT 1
> FROM sec_Permissions
> WHERE sec_Permissions.UserID = sec_Users.UserID
> AND sec_Permissions.RoleID = sec_Roles.RoleID
> )
> UNION
> SELECT sec_Users.UserID,
> sec_Roles.Descriptor,
> 0
> FROM sec_Users,
> sec_Roles
> WHERE NOT EXISTS (
> SELECT 1
> FROM sec_Permissions
> WHERE sec_Permissions.UserID = sec_Users.UserID
> AND sec_Permissions.RoleID = sec_Roles.RoleID
> )
>
>
>
> Ivaylo Ivanov wrote:
>> Something like this:
>>
>> select users.usr_name, roles.role_name,
>> (select sign(count(*))
>> from permissions
>> where users.usr_id = permissions.usr_id and roles.role_id =
>> permissions.role_id) as existence
>> from users, roles
>> order by 1, 2
>>
>> Regards,
>> Ivaylo
>>
>> "Jason 'Bug' Fenter [TeamSybase]" <jason.fenter@teamsybase.com> wrote in
>> message news:4adf632f$1@forums-1-dub...
>>> I've done and gone ignorant.
>>>
>>> I have a "users" table and a "roles" table. I have a "permissions" table
>>> that manages the many-to-many relationship between users and roles. I
>>> need a SQL statement that gives me all users and all roles with a flag
>>> indicating the existence (or non-existence) of a user's association with
>>> a role.
>>>
>>> In other words, imagine user "JDoe" who is associated with roles
>>> "Security" and "Public". Image user "SRandom" associated with roles
>>> "Admin" and "Public". Image a role of "Sales" with no users associated
>>> with it. I need a query that brings back this result set:
>>>
>>> JDoe, Public, 1
>>> JDoe, Admin, 0
>>> JDoe, Security, 1
>>> JDoe, Sales, 0
>>> SRandom, Public, 1
>>> SRandom, Admin, 1
>>> SRandom, Security, 0
>>> SRandom, Sales, 0
>>>
>>>
>>> I could do some aggregation in the datawindow itself, but I *should
>>> know* this SQL and it's really bugging me!
>>


M. Searer Posted on 2009-10-21 19:53:51.0Z
From: "M. Searer" <nospam@nospam.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4adf632f$1@forums-1-dub>
In-Reply-To: <4adf632f$1@forums-1-dub>
Subject: Re: Which join?
Lines: 30
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4adf66cf$1@forums-1-dub>
Date: 21 Oct 2009 12:53:51 -0700
X-Trace: forums-1-dub 1256154831 10.22.241.152 (21 Oct 2009 12:53:51 -0700)
X-Original-Trace: 21 Oct 2009 12:53:51 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90119
Article PK: 409725

use a cross join

"Jason 'Bug' Fenter [TeamSybase]" <jason.fenter@teamsybase.com> wrote in message news:4adf632f$1@forums-1-dub...
> I've done and gone ignorant.
>
> I have a "users" table and a "roles" table. I have a "permissions" table
> that manages the many-to-many relationship between users and roles. I
> need a SQL statement that gives me all users and all roles with a flag
> indicating the existence (or non-existence) of a user's association with
> a role.
>
> In other words, imagine user "JDoe" who is associated with roles
> "Security" and "Public". Image user "SRandom" associated with roles
> "Admin" and "Public". Image a role of "Sales" with no users associated
> with it. I need a query that brings back this result set:
>
> JDoe, Public, 1
> JDoe, Admin, 0
> JDoe, Security, 1
> JDoe, Sales, 0
> SRandom, Public, 1
> SRandom, Admin, 1
> SRandom, Security, 0
> SRandom, Sales, 0
>
>
> I could do some aggregation in the datawindow itself, but I *should
> know* this SQL and it's really bugging me!


Scott Morris Posted on 2009-10-21 20:37:39.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4adf632f$1@forums-1-dub> <4adf66cf$1@forums-1-dub>
Subject: Re: Which join?
Lines: 41
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4adf7113$1@forums-1-dub>
Date: 21 Oct 2009 13:37:39 -0700
X-Trace: forums-1-dub 1256157459 10.22.241.152 (21 Oct 2009 13:37:39 -0700)
X-Original-Trace: 21 Oct 2009 13:37:39 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90123
Article PK: 409729


"M. Searer" <nospam@nospam.com> wrote in message
news:4adf66cf$1@forums-1-dub...
> use a cross join

Example:

set nocount on
create table #user (
user_name varchar(20) not null
)
create table #role (
role_name varchar(20) not null
)
create table #user_role (
user_name varchar(20) not null
, role_name varchar(20) not null
)

insert #user (user_name)
select 'JDoe' union all select 'SRandom'

insert #role (role_name)
select 'Security' union all select 'Public' union all select 'Admin'

insert #user_role (user_name, role_name)
select 'JDoe', 'Public' union all
select 'JDoe', 'Security' union all
select 'SRandom', 'Admin'

select usr.user_name, rle.role_name, case when usrle.user_name is null then
0 else 1 end as 'assigned'
from #user as usr cross join #role as rle
left join #user_role as usrle
on usr.user_name = usrle.user_name
and rle.role_name = usrle.role_name
order by usr.user_name, rle.role_name

Could also change the left join into a correlated subquery - which is more
efficient is something left to further investigation.


Scott Morris Posted on 2009-10-21 20:39:42.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4adf632f$1@forums-1-dub> <4adf66cf$1@forums-1-dub> <4adf7113$1@forums-1-dub>
Subject: Re: Which join?
Lines: 3
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4adf718e$1@forums-1-dub>
Date: 21 Oct 2009 13:39:42 -0700
X-Trace: forums-1-dub 1256157582 10.22.241.152 (21 Oct 2009 13:39:42 -0700)
X-Original-Trace: 21 Oct 2009 13:39:42 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90124
Article PK: 409727

Should have noted that this is valid for SQL Server.


M. Searer Posted on 2009-10-28 16:25:33.0Z
From: "M. Searer" <nospam@nospam.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4adf632f$1@forums-1-dub> <4adf66cf$1@forums-1-dub> <4adf7113$1@forums-1-dub> <4adf718e$1@forums-1-dub>
In-Reply-To: <4adf718e$1@forums-1-dub>
Subject: Re: Which join?
Lines: 7
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ae8707d$1@forums-1-dub>
Date: 28 Oct 2009 08:25:33 -0800
X-Trace: forums-1-dub 1256747133 10.22.241.152 (28 Oct 2009 08:25:33 -0800)
X-Original-Trace: 28 Oct 2009 08:25:33 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90164
Article PK: 409768

I believe it also valid for oracle and sql anywhere
IIRC, ASE does not support cross joins. ASE has some ommissions in its support for ANSI sql.

"Scott Morris" <bogus@bogus.com> wrote in message news:4adf718e$1@forums-1-dub...
> Should have noted that this is valid for SQL Server.
>
>