38 users online (2 members and 36 guests)  


  Results 1 to 11 of 11

Related

  1. Replies: 1
  2. Replies: 0
  3. selecting more then it should    Forum: PHP Forum
    Replies: 2
  4. Replies: 4
  1. #1
    Rekcor's Avatar
    New User

    Status
    Offline
    Join Date
    Feb 2005
    Posts
    6

    MySQL - Selecting rows which are not in 2 lookuptables

    For my CMS, I've got two lookuptables

    * pagereadlookup

    +-----------------+
    | userId | pageID |
    +-----------------+

    in which userIds are coupled to pageId (so it says which user may read which page)

    * pagewritelookup

    +-----------------+
    | userId | pageID |
    +-----------------+

    similar to pagereadlookup.

    And a table with page properties

    * page
    +-----------------+
    | ID | name | URL |
    +-----------------+

    What I want is, given an user with userId=x, to select all pages which this user may both NOT read and NOT write.

    I've tried a lot but I can't figure this out. Who can help me?
    Last edited by Rekcor; 02-16-2005 at 10:30 AM. Reason: (wrong title)

  2. #2
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662
    Edit: oops says mysql in the title.

    Should be able to do this with a JOIN. Will have a quick play and post back.
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?

  3. #3
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662
    Hmm. the mySQL Manual gives us a good beginning

    Code:
    mysql> SELECT table1.* FROM table1
        ->        LEFT JOIN table2 ON table1.id=table2.id
        ->        WHERE table2.id IS NULL;
    Now what this does is selects from table1 where their ID is NOT present in table2. Good start. This gives us a good example of how to choose negative results, we can use IS NULL.

    Still, we need to grab from two tables, and we need to specify the users ID.

    I am enjoying this question, it's forcing me to really understand JOINS much more than the simple stuff I have used before. Thanks for the good question

    I will post back as I get further.
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?

  4. #4
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662
    Right, this may work but I can't test it properly without recreating all your tables.

    Code:
    select * from (page LEFT JOIN readpagelookup ON readpagelookup.userID='$userID') 
     LEFT JOIN writepagelookup ON writepagelookup.userID='$userID' WHERE page.ID is NULL;
    Might work, might not. Can't tell. Try it, tell me what it says. It would also help if you could export your tables and some test data so I can try it if it fails.
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?

  5. #5
    Rekcor's Avatar
    New User

    Status
    Offline
    Join Date
    Feb 2005
    Posts
    6
    Hi,

    Thanks for your idea. It didn't work though. Let me first show how the different tables contain (then it is easier for me to explain the results)

    Code:
    *pagereadlookup
    
    +-----------------+
    | userId | pageId |
    +-----------------+
    |---2---------1---|
    +-----------------+
    |---2---------4---|
    +-----------------+
    |---3---------2---|
    +-----------------+
    
    *pagewritelookup
    
    +-----------------+
    | userId | pageId |
    +-----------------+
    |---1---------1---|
    +-----------------+
    |---1---------2---|
    +-----------------+
    |---1---------3---|
    +-----------------+
    |---1---------4---|
    +-----------------+
    |---3---------1---|
    +-----------------+
    
    *page
    +----------+
    | Id | URL |
    +----------+
    |-1------A-|
    +----------+
    |-2------B-|
    +----------+
    |-3------C-|
    +----------+
    |-4------D-|
    +----------+
    The query you gave

    Code:
    select * from 
    (page LEFT JOIN pagereadlookup ON pagereadlookup.userID=2) 
     LEFT JOIN pagewritelookup ON pagewritelookup.userID=2
    WHERE page.ID is NULL;

    actually returned nothing at all. I removed the last part:

    Code:
    WHERE page.ID is NULL;
    and then it returned:

    Code:
    | ID | URL | userId | pageId | userId | pageId |
    | 1  | A   | 2      | 1      | NULL   | NULL   | 
    | 1  | A   | 2      | 4      | NULL   | NULL   | 
    | 2  | B   | 2      | 1      | NULL   | NULL   | 
    | 2  | B   | 2      | 4      | NULL   | NULL   | 
    | 3  | C   | 2      | 1      | NULL   | NULL   | 
    | 3  | C   | 2      | 4      | NULL   | NULL   | 
    | 4  | 1   | 2      | 1      | NULL   | NULL   | 
    | 4  | 1   | 2      | 4      | NULL   | NULL   |
    Mmm... don't know what to do..

    Regards,
    Koos

    P.S. Do you know if it is possible to set the font of this small online editor (in which im typing now) to Courier? Making these tables took me about 25 minutes, because the character-width of font is not equal for all characters..

  6. #6
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662
    I think I'll recreate your tables, easiest for me to test then. Will take me a while though.

    For the layout, I usually just use a text editor with my own font, and it always comes out fine between the CODE tags.
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?

  7. #7
    Rekcor's Avatar
    New User

    Status
    Offline
    Join Date
    Feb 2005
    Posts
    6
    Ok, that would be wonderful, thank you in advance!

    Regards,
    Koos

  8. #8
    Rekcor's Avatar
    New User

    Status
    Offline
    Join Date
    Feb 2005
    Posts
    6

    Solution?

    Hi,

    Ive been trying to first simplify my question to 1 lookuptable, and this works

    Code:
    select *
    from 
    page 
    LEFT JOIN pagereadlookup on ID=pagereadlookup.pageId 
    WHERE (
    pagereadlookup.userId is NULL OR pagereadlookup.userId!=2)
    But I can't figure out how to extend this query to both lookuptables,

    Code:
    SELECT * 
    FROM (
    page
    LEFT JOIN pagereadlookup ON ID = pagereadlookup.pageId
    WHERE (
    pagereadlookup.userId IS NULL 
    OR pagereadlookup.userId !=2)
    )
    LEFT JOIN pagewritelookup ON ID = pagewritelookup.pageId
    WHERE (
    pagewritelookup.userId IS NULL 
    OR pagewritelookup.userId !=2)
    gives an error:

    Code:
    #1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (pagereadlookup.userId is NULL OR pagereadlookup.userId!=
    Do you think I'm on the right track? Thanks!

    Koos

  9. #9
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662
    Your on the right track, I got one working but not two. Its (relatively) complicated syntax though
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?

  10. #10
    Rekcor's Avatar
    New User

    Status
    Offline
    Join Date
    Feb 2005
    Posts
    6
    Ok thanx.

    Maybe a weak decision, but I gave up.

    I wrote a script in PHP which mimics the query we where looking for (it first selects al pages and than substracts the pages the user may read and write).

  11. #11
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662
    Can't say I blame you. Its a larger dataset but it should still scale quite well.
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?



Tags for this Thread