Thread: Must I use DISTINCT?

Must I use DISTINCT?

From

Michael B Allen

Date:

05 February 2009, 20:57:44

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.

Mike

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

Re: Must I use DISTINCT?

From

Rajesh Kumar Mallah

Date:

06 February 2009, 02:59:17

have you tried Join using , egSELECT 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.

regds
rajesh kumar mallah.

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.
>
> Mike
>
> --
> Michael B Allen
> Java Active Directory Integration
> http://www.ioplex.com/
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Re: Must I use DISTINCT?

From

Michael B Allen

Date:

06 February 2009, 03:54:55

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/

Re: Must I use DISTINCT?

From

Stephan Szabo

Date:

06 February 2009, 05:11:57

On Thu, 5 Feb 2009, Michael B Allen 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.

Would something like:SELECT e.eid, e.name FROM entry e WHERE e.eid = 120 AND(e.ownid = 66 OR e.aid in (select a.aid
fromaccess a))
 
do what you wnat?

Re: Must I use DISTINCT?

From

Raj Mathur

Date:

06 February 2009, 05:22:25

On Friday 06 Feb 2009, Michael B Allen wrote:
> 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 |
>
> +-----+---------------------------------------+-------+-------+
> | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |    66 |     0 |
> +-----+---------------------------------------+-------+-------+
> 15 rows in set (0.01 sec)
>
> > select a, b from foo_link;
>
> +-----+-----+
>
> | a   | b   |
>
> +-----+-----+
> |  71 |  92 |
> +-----+-----+
> 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.

Something like this?

select * from foo_entry where eid = 113 and ownid in (select a from 
foo_link where a=66 or b=66);

Regards,

-- Raju
-- 
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/      GPG: 78D4 FC67 367F 40E2 0DD5  0FEF C968
D0EFCC68 D17F
 
PsyTrance & Chill: http://schizoid.in/   ||   It is the mind that moves