SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2002
    Location
    Calgary
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Locker Room Help

    Funny title.

    I am looking at a problem that I can solve with Oracle by using a out join. But I am a little stuck with mysql.

    I have a course "ABC"

    I have a total of 15 students in my class.

    8 have registered for the course "ABC" and 7 haven't as of yet.

    I am a little confused with the DB Logic to solve this.

    Thanks for you help.

  2. #2
    + platinum's Avatar
    Join Date
    Jun 2001
    Location
    Adelaide, Australia
    Posts
    6,441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you post your table structure?

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2002
    Location
    Calgary
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is going to be a little more complex then suggested above, but ok.

    #
    # Table structure for table `ss_client_list`
    #

    CREATE TABLE ss_client_list (
    person_id bigint(16) NOT NULL default '0',
    account_id bigint(16) NOT NULL default '0',
    PRIMARY KEY (person_id,account_id)
    ) TYPE=MyISAM;

    #
    # Dumping data for table `ss_client_list`
    #

    INSERT INTO ss_client_list VALUES (2, 1);
    INSERT INTO ss_client_list VALUES (3, 1);
    INSERT INTO ss_client_list VALUES (4, 1);
    INSERT INTO ss_client_list VALUES (5, 1);
    # --------------------------------------------------------

    #
    # Table structure for table `ss_permissions`
    #

    CREATE TABLE ss_permissions (
    file_id bigint(16) NOT NULL default '0',
    account_id bigint(16) NOT NULL default '0',
    client_id bigint(16) NOT NULL default '0',
    read char(1) NOT NULL default 'N',
    over_write char(1) NOT NULL default 'N',
    download char(1) NOT NULL default 'N',
    upload char(1) NOT NULL default 'N',
    rename char(1) NOT NULL default 'N',
    delete char(1) NOT NULL default 'N',
    move char(1) NOT NULL default 'N',
    PRIMARY KEY (file_id,account_id,client_id)
    ) TYPE=MyISAM;

    #
    # Dumping data for table `ss_permissions`
    #

    INSERT INTO ss_permissions VALUES (1, 1, 2, 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
    INSERT INTO ss_permissions VALUES (1, 1, 4, 'N', 'N', 'N', 'N', 'N', 'N', 'N');
    INSERT INTO ss_permissions VALUES (0, 1, 2, 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
    INSERT INTO ss_permissions VALUES (0, 1, 4, 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
    INSERT INTO ss_permissions VALUES (1, 1, 3, 'N', 'N', 'N', 'N', 'N', 'N', 'N');
    # --------------------------------------------------------

    #
    # Table structure for table `ss_person`
    #

    CREATE TABLE ss_person (
    person_id bigint(16) NOT NULL auto_increment,
    user_id text NOT NULL,
    password tinytext NOT NULL,
    first_name mediumtext NOT NULL,
    last_name mediumtext NOT NULL,
    email mediumtext NOT NULL,
    address mediumtext NOT NULL,
    city mediumtext NOT NULL,
    stateorprovince mediumtext NOT NULL,
    postalcode mediumtext NOT NULL,
    country mediumtext NOT NULL,
    companyname mediumtext NOT NULL,
    title mediumtext NOT NULL,
    workphone mediumtext NOT NULL,
    workextension mediumtext NOT NULL,
    homephone mediumtext NOT NULL,
    mobilephone mediumtext NOT NULL,
    faxnumber mediumtext NOT NULL,
    credit_card text NOT NULL,
    PRIMARY KEY (person_id)
    ) TYPE=MyISAM;

    #
    # Dumping data for table `ss_person`
    #

    INSERT INTO ss_person VALUES (1, 'lfabbric', '4924d867c3513ac4d196d704394030c9', 'Landon', 'Fabbricino', 'lfabbric@smartshare.ca', '', '', '', '', '', '', '', '', '', '', '', '', '');
    INSERT INTO ss_person VALUES (2, 'dbelanger', '4924d867c3513ac4d196d704394030c9', 'Diane', 'Belanger', 'dvb80@hotmail.com', '', '', '', '', '', '', '', '', '', '', '', '', '');
    INSERT INTO ss_person VALUES (3, 'mdias', '4924d867c3513ac4d196d704394030c9', 'Meghan', 'Dias', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
    INSERT INTO ss_person VALUES (4, 'mbelanger', '4924d867c3513ac4d196d704394030c9', 'Michelle', 'Belanger', 'belcomm@shaw.ca', '', '', '', '', '', '', '', '', '', '', '', '', '');
    INSERT INTO ss_person VALUES (5, 'ktaylor', 'ktaylor', 'Karen', 'Taylor', '', '', '', '', '', '', '', '', '', '', '', '', '', '');



    Now the one query I use to show all users with permission would be.

    SELECT a.first_name, a.last_name
    FROM ss_person a, ss_permissions b, ss_client_list c
    WHERE a.person_id = b.client_id
    AND a.person_id = c.person_id
    AND b.account_id = c.account_id
    AND b.file_id = 1
    AND b.account_id = 1

    I am looking for all people who is in the client list, but does not have permissions.

    I believe I need to do an outer join.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    SELECT a.first_name, a.last_name
    FROM ss_person a
    left outer
    join ss_permissions b
    on a.person_id = b.client_id
    left outer
    join ss_client_list c
    on a.person_id = c.person_id
    and b.account_id = c.account_id
    where b.file_id = 1
    and b.account_id = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Santos L Halper Zenith's Avatar
    Join Date
    May 2002
    Location
    Finland
    Posts
    641
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    table 1: courses
    - courseid
    - coursename
    - (more fields)

    table 2: persons
    - personid
    - personname
    - (more fields)

    table 3: personscoursers
    - pkid
    - personid
    - courseid

    when person registers to a course X, you add a row to to table 3: id of the course and the person. When you want to know which persons have registered you can do it like this:

    select persons.personid, personcourses.courseid from persons left outer join personscourses on persons.personid = personscoursers.personid

    It'd list personid + courseid from persons that have reqistered and personid + 0 meaning not registered yet. You can always change "left outer join" to "inner join" when you only get a list of people that have registered.

    -Z-

  6. #6
    SitePoint Zealot
    Join Date
    Sep 2002
    Location
    Calgary
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937.

    I tried

    SELECT a.first_name, a.last_name
    FROM ss_person a
    left outer
    join ss_permissions b
    on a.person_id = b.client_id
    left outer
    join ss_client_list c
    on a.person_id = c.person_id
    and b.account_id = c.account_id
    where b.file_id = 1
    and b.account_id = 1

    But it did not work. It showed all people registered to a course.
    It showed ID, 2,3,4. All I want to see is ID 5.

    Thanks again for all of your help

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, sorry, i overlooked one part of your original problem -- "looking for all people who is in the client list, but does not have permissions"

    replace the WHERE clause in your query with this one:

    where b.client_id is null

    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Sep 2002
    Location
    Calgary
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I must be missing something. I replaced the query, I am now returning nothing.

    I am sorry to be a burdan.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you replaced the query with what?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Sep 2002
    Location
    Calgary
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT a.first_name, a.last_name
    FROM ss_person a
    left outer
    join ss_permissions b
    on a.person_id = b.client_id
    left outer
    join ss_client_list c
    on a.person_id = c.person_id
    and b.account_id = c.account_id
    where b.client_id = null
    and b.account_id = 1

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    where b.client_id = null
    and b.account_id = 1

    b.client_id will be null whenever there is a person without a permission

    in fact, all columns from b will be null in that case

    therefore, if b.client_id is null, so will b.account_id, and therefore it won't be 1, so that's why you get no results

    rudy

  12. #12
    SitePoint Zealot
    Join Date
    Sep 2002
    Location
    Calgary
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So then their is no way to determine all people who do not have permission to a file.

    Well thanks for the help.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, there is a way

    b.client_id will be null whenever there is a person without a permission

    if you test for that, you get ss_person rows without a matching ss_permissions row

    if b.account_id = 1 is a required condition (you never explained why you stuck that back in there), then move it into the ON clause of the join

    rudy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •