query help appreciated 
Author Message
 query help appreciated

hopefully this question can be understood in general terms
without having to post the table definitions, etc. i'd
like some conceptual help how to create a query.

i have 3 tables. 'users' contains a list of names. 'files'
contains a list of file names. 'security' contains which
users have access to which files. example:

if the files table has entries for files named 1 thru 4
then the security table could look like:

user | file
bob    1
bob    2
tom    1
tom    3
john   2
tim    4

i'd like to make a query that returns the list of user
names that have access to any file that i also have access
to. if i'm bob (therefore i have access to files 1 & 2)
the results returned would be:

bob (me, access to 1,2)
tom  (because of access to 1)
john (because of access to 2)

hope that makes sense. just not sure if it can be done
with joins or if i need a subquery, etc. it's obvious how
to query what i have access to but how do i compare my
results to everyone else's? thanks.
ms



Tue, 10 May 2005 03:49:26 GMT
 query help appreciated

Try:



SELECT DISTINCT [user]
  FROM #tbl
 WHERE [file] IN (SELECT [file]
                    FROM #tbl

--
- Anith



Tue, 10 May 2005 03:56:04 GMT
 query help appreciated
Try something like

select user, file
from security
where file in (select file from securtity where user='bob')


Quote:
> hopefully this question can be understood in general terms
> without having to post the table definitions, etc. i'd
> like some conceptual help how to create a query.

> i have 3 tables. 'users' contains a list of names. 'files'
> contains a list of file names. 'security' contains which
> users have access to which files. example:

> if the files table has entries for files named 1 thru 4
> then the security table could look like:

> user | file
> bob    1
> bob    2
> tom    1
> tom    3
> john   2
> tim    4

> i'd like to make a query that returns the list of user
> names that have access to any file that i also have access
> to. if i'm bob (therefore i have access to files 1 & 2)
> the results returned would be:

> bob (me, access to 1,2)
> tom  (because of access to 1)
> john (because of access to 2)

> hope that makes sense. just not sure if it can be done
> with joins or if i need a subquery, etc. it's obvious how
> to query what i have access to but how do i compare my
> results to everyone else's? thanks.
> ms



Tue, 10 May 2005 04:14:08 GMT
 query help appreciated
Thank you! it looks like that was exactly what i wanted.
looks much simpler than i expected. wish i could have
figured it out.

Quote:
>-----Original Message-----
>hopefully this question can be understood in general
terms
>without having to post the table definitions, etc. i'd
>like some conceptual help how to create a query.

>i have 3 tables. 'users' contains a list of
names. 'files'
>contains a list of file names. 'security' contains which
>users have access to which files. example:

>if the files table has entries for files named 1 thru 4
>then the security table could look like:

>user | file
>bob    1
>bob    2
>tom    1
>tom    3
>john   2
>tim    4

>i'd like to make a query that returns the list of user
>names that have access to any file that i also have
access
>to. if i'm bob (therefore i have access to files 1 & 2)
>the results returned would be:

>bob (me, access to 1,2)
>tom  (because of access to 1)
>john (because of access to 2)

>hope that makes sense. just not sure if it can be done
>with joins or if i need a subquery, etc. it's obvious how
>to query what i have access to but how do i compare my
>results to everyone else's? thanks.
>ms

>.



Tue, 10 May 2005 05:40:45 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. query help appreciated. max()/joins/subquery

2. query help appreciated

3. Query Challenge - Help Appreciated

4. Simple Contains Query Question (any help appreciated)

5. slow query -- help much appreciated

6. Complex query, some help would be appreciated

7. Help for query interface design would be highly appreciated

8. Ado Help Please Help any help is appreciated

9. sql query - comments appreciated

10. IMMEDIATE help appreciated

11. ****ANY HELP APPRECIATED**********

12. Appreciate your help


 
Powered by phpBB® Forum Software