Re: Must I use DISTINCT?

On Thu, Feb 5, 2009 at 10:59 PM, Rajesh Kumar Mallah
<mallah(dot)rajesh(at)gmail(dot)com> wrote:
> have you tried Join using , eg
> SELECT e.eid, e.name
> FROM entry e join access a ON( e.eid = 120
> AND (e.ownid = 66 OR e.aid = a.aid) ) ;
>
> some sample data might also help in understanding the prob
> more clrearly.

Hi Rajesh,

Unfortunately using JOIN does not seem to change the result.

Here is some real data:

> select eid, name, ownid, aclid from foo_entry;
+-----+---------------------------------------+-------+-------+
| eid | name | ownid | aclid |
+-----+---------------------------------------+-------+-------+
| 64 | system | 64 | 0 |
| 66 | abaker | 66 | 0 |
| 67 | bcarter | 67 | 0 |
| 68 | cdavis | 68 | 0 |
| 69 | abaker(at)example(dot)com | 66 | 114 |
| 70 | bcarter(at)example(dot)com | 67 | 120 |
| 71 | cdavis(at)example(dot)com | 68 | 0 |
| 72 | (201) 555-1234 | 66 | 0 |
| 73 | (201) 555-4321 | 67 | 0 |
| 74 | (908) 555-2341 | 68 | 0 |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | 66 | 0 |
| 92 | IOPLEX Software | 64 | 0 |
| 114 | Small Cap Consultants | 66 | 0 |
| 120 | Equity Report Readers | 66 | 0 |
| 111 | proton.foo.net | 64 | 0 |
+-----+---------------------------------------+-------+-------+
15 rows in set (0.01 sec)

> select a, b from foo_link;
+-----+-----+
| a | b |
+-----+-----+
| 64 | 111 |
| 64 | 113 |
| 66 | 69 |
| 66 | 72 |
| 66 | 113 |
| 66 | 114 |
| 67 | 70 |
| 67 | 89 |
| 67 | 113 |
| 68 | 71 |
| 68 | 113 |
| 69 | 72 |
| 70 | 73 |
| 71 | 74 |
| 71 | 92 |
| 114 | 120 |
+-----+-----+
16 rows in set (0.00 sec)

So there are two tables: foo_entry AS e and foo_link AS a1. I want to
select the the single row from foo_entry with e.eid = 113 but only if
the e.ownid = 66 OR if e.aclid is indirectly linked with 66 via the
foo_link table.

SELECT e.eid, e.name
FROM foo_entry e
JOIN foo_link a1 ON (e.eid = 113 AND (e.ownid = 66 OR (e.aclid = a1.a
AND a1.b = 66)))

This yields:

+-----+---------------------------------------+
| eid | name |
+-----+---------------------------------------+
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
+-----+---------------------------------------+
16 rows in set (0.00 sec)

So it matched eid 113 because e.ownid is 66 but it also matched
duplicates for each entry in the foo_link table because the is nothing
to constrain it with foo_link.

Ultimately what I'm trying to do is implement simple access lists in
SQL. The foo_link table represents links between account and access
list entries. So to select an entry, you either need to own it (ownid
= 66) or your account id is linked with it through an access list
entry (e.aclid = a1.a AND a1.b = 66).

If I use DISTINCT it works fine but it just does not feel right. I'm
worried that as the number of links increases (there could be
thousands) performance be negatively affected.

Mike

> On Fri, Feb 6, 2009 at 3:27 AM, Michael B Allen <ioplex(at)gmail(dot)com> wrote:
>> Please consider the following SQL
>>
>> SELECT e.eid, e.name
>> FROM entry e, access a
>> WHERE e.eid = 120
>> AND (e.ownid = 66 OR e.aid = a.aid)
>>
>> The intent is to match one entry with the eid of 120. However I would
>> like to impose an additional constraint that either e.ownid must be 66
>> or e.aid must match the aid of an entry in the access table (there's
>> actually a lot more to the query but I think this should be sufficient
>> to illustrate my problem).
>>
>> The problem is that the e.ownid is 66 and therefore the same entry is
>> returned for each access entry.
>>
>> Of course I can simply SELECT DISTINCT but that seems like an improper
>> usage of DISTINCT here.
>>
>> Is there an alternative way to write this query? I only want to select
>> from the access table for the purpose of constraining by aid.
>>

--
Michael B Allen
Java Active Directory Integration
http://www.ioplex.com/