Re: Must I use DISTINCT? - Mailing list pgsql-sql

On Thu, Feb 5, 2009 at 10:59 PM, Rajesh Kumar Mallah
<mallah.rajesh@gmail.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@example.com                    |    66 |   114 |
|  70 | bcarter@example.com                   |    67 |   120 |
|  71 | cdavis@example.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@gmail.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/