Re: SQL Question

[Oded Arbel]

Shlomi Fish wrote:

On Fri, 16 Aug 2002, Oded Arbel wrote:

  

Shlomi Fish wrote:

    

I have three tables: clubs, users and permissions. clubs contains a
Club_ID (an integer). users contains a User_ID. Permissions contains both
as well as a Subjects field (a boolean).

Now, a user is allowed to edit the data associated with a club if he has a
record in the permissions table (with the appropriate User_ID and
Club_ID). He is allowed to edit the subjects if the Subjects field is set.

Now, I want to make one query that given a User_ID will return a list of
all the clubs in clubs with the following three fields:

1. Club_ID
2. Whether a record exist in Permissions.
3. If so, whether the Subjects flag is set (or if 2 is false -
undetermined)

I am using MySQL 3.23.47, but can possibly switch to PostgreSQL.


      

You need to do a left outer join on the clubs and permissions table, something which MySQL 3 does not support. 4.1 should do it, but I have only 4.0 installed and I can't make it it the query Arik came up with. I don't know if PostgreSQL can do it.

Credits: Arik Baratz, our resident SQL guru came up with the correct query. if you're still interested I can send it.

    

Hi,

please send it to me.

  

Ok, goes something like this :

SELECT
    clubs.id,
    editable,
    subjects
FORM clubs,
LEFT OUTER JOIN
    (
        SELECT
            'yes' AS editable
            permissions.subjects AS subjects
            permissions.club_id AS club_id
        FROM
            permissions
        WHERE
            permissions.user_id = :requested_user
    )
ON clubs.id = club_id

This - as stated before - does not work in MySQL currently (Maybe in 4.1). alternativly, you can do it with a UNION, as so :

(
    SELECT
        clubs.id AS club_id,
        'yes' AS editable,
        permissions.subjects
    FROM
        clubs,
        permissions
    WHERE
        clubs.id = permissions.club_id
        AND
        permissions.user_id = :requested_user
)
UNION
(
    SELECT
        clubs.id AS club_id,
        'no' AS editable,
        permissions.subjects
    FORM
        clubs,
        permissions
    WHERE
        clubs.id = permissions.club_id
        AND
        permissions.user_id != :requested_user
)
ORDER BY club_id;

a much longer query, but it works on my MySQL 4.0.1, but again - won't work on MySQL 3.x
sorry

-- 
Oded

::..
Computer Science: solving today's problems tomorrow.