Обсуждение: Must I use DISTINCT?
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/
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 >
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/
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?
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