Hi,

I have a table of users

TABLE_A = username, userID

They are member of one _or more_ group

TABLE_B = userID, grouID

Each group has a set of one _or more_ permissions

TABLE_C = grouID, permission

So to get the permissions for one user I could do 3 queries.

SELECT userID FROM TABLE_A where username = 'x'
SELECT groupID FROM TABLE_B where userID = TABLE_A.userID
SELECT permission FROM TABLE_C where groupID = TABLE_B.groupID

How could I do the above in one simple query?

The table are really that simple, but I want to keep them separated
because each groupID will have a name and each permissionID will also
have a name.

How would you simplify those 3 queries?

Thanks
FFMG


--

'webmaster forum' (http://www.httppoint.com) | 'Free Blogs'
(http://www.journalhome.com/) | 'webmaster Directory'
(http://www.webhostshunter.com/)
'Recreation Vehicle insurance'
(http://www.insurance-owl.com/other/car_rec.php) | 'Free URL
redirection service' (http://urlkick.com/)
------------------------------------------------------------------------
FFMG's Profile: http://www.httppoint.com/member.php?userid=580
View this thread: http://www.httppoint.com/showthread.php?t=21905

Message Posted via the webmaster forum http://www.httppoint.com, (Ad revenue sharing).

FFMG <FFMG.2zlyes@no-mx.httppoint.com> wrote in
<FFMG.2zlyes@no-mx.httppoint.com>:
> I have a table of users
>
> TABLE_A = username, userID
>
> They are member of one _or more_ group
>
> TABLE_B = userID, grouID
>
> Each group has a set of one _or more_ permissions
>
> TABLE_C = grouID, permission
>
> So to get the permissions for one user...

....you should use JOINs. Read about JOIN syntax in MySQL
Reference Manual.

--
"I can't but wonder if you... don't know a hell of a
lot more about practically every subject than Solomon ever
did."

Pavel Lepin;101603 Wrote:
>
> ....you should use JOINs. Read about JOIN syntax in MySQL
> Reference Manual.
>
> --
> "I can't but wonder if you... don't know a hell of a
> lot more about practically every subject than Solomon ever
> did."

Thanks, I did look at JOINS

SELECT * from TABLE_A
join TABLE_B on TABLE_A.userId = TABLE_B.userId
join TABLE_C on TABLE_B.groupId = TABLE_C.groupId
WHERE
TABLE_A.username = 'x'

But I am not sure this is the most efficient way of doing it.

FFMG


--

'webmaster forum' (http://www.httppoint.com) | 'Free Blogs'
(http://www.journalhome.com/) | 'webmaster Directory'
(http://www.webhostshunter.com/)
'Recreation Vehicle insurance'
(http://www.insurance-owl.com/other/car_rec.php) | 'Free URL
redirection service' (http://urlkick.com/)
------------------------------------------------------------------------
FFMG's Profile: http://www.httppoint.com/member.php?userid=580
View this thread: http://www.httppoint.com/showthread.php?t=21905

Message Posted via the webmaster forum http://www.httppoint.com, (Ad revenue sharing).

Pavel Lepin;101603 Wrote:
>
>
> ....you should use JOINs. Read about JOIN syntax in MySQL
> Reference Manual.
>
> --
> "I can't but wonder if you... don't know a hell of a
> lot more about practically every subject than Solomon ever
> did."

Sorry for the double reply.

But why would a JOIN be better than a multiple select?

I could so

SELECT TABLE_C.* FROM TABLE_A, TABLE_B, TABLE_B
WHERE
TABLE_C.permission = TABLE_B.permission
AND
TABLE_B.userID = TABLE_A.userID
AND
TABLE_A.username = 'x'

And in my case get the same result, what would be the 'better' way of
selecting the permissions?

FFMG


--

'webmaster forum' (http://www.httppoint.com) | 'Free Blogs'
(http://www.journalhome.com/) | 'webmaster Directory'
(http://www.webhostshunter.com/)
'Recreation Vehicle insurance'
(http://www.insurance-owl.com/other/car_rec.php) | 'Free URL
redirection service' (http://urlkick.com/)
------------------------------------------------------------------------
FFMG's Profile: http://www.httppoint.com/member.php?userid=580
View this thread: http://www.httppoint.com/showthread.php?t=21905

Message Posted via the webmaster forum http://www.httppoint.com, (Ad revenue sharing).

FFMG <FFMG.2zmfl0@no-mx.httppoint.com> wrote in
<FFMG.2zmfl0@no-mx.httppoint.com>:
> Pavel Lepin Wrote:
>> ....you should use JOINs. Read about JOIN syntax in MySQL
>> Reference Manual.
>
> But why would a JOIN be better than a multiple select?

"Multiple select?"

> SELECT TABLE_C.* FROM TABLE_A, TABLE_B, TABLE_B
> WHERE
> TABLE_C.permission = TABLE_B.permission
> AND
> TABLE_B.userID = TABLE_A.userID
> AND
> TABLE_A.username = 'x'

You haven't read the chapter on JOINs, did you? I'll quote
from MySQL 5.0 Reference Manual, 12.2.7.1 for you:

INNER JOIN and , (comma) are semantically equivalent in
the absence of a join condition: both produce a Cartesian
product between the specified tables (that is, each and
every row in the first table is joined to each and every
row in the second table).

So in a very real sense, this query still uses JOINs. You're
simply using WHERE clause instead of join condition to
filter the result set.

> And in my case get the same result, what would be the
> 'better' way of selecting the permissions?

The result set will be the same. Some people find the syntax
with explicit JOINs and join conditions less obscure and
more to the point. YMMV. I remember a reasonably
knowledgeable person saying mysqld might have a harder time
optimising queries using WHERE clause instead of join
conditions, but I'm not a query optimisation expert myself,
so don't quote me on that. If in doubt, run benchmarks.

--
"I can't but wonder if you... don't know a hell of a
lot more about practically every subject than Solomon ever
did."