SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    Houston, Texas, USA
    Posts
    559
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql outer join query problem

    How do I join the following two tables to get the results I need?

    Table a is called webpages and has the following field:
    webpage_id number

    The records found in table a are:

    webpage_id
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11

    Table b is called webrole_permissions and has the following fields:
    webpage_id number
    role_id number

    The records found in table b are:

    role_id webpage_id
    20 6
    20 7
    20 11
    21 9
    22 7
    22 10
    22 12


    I want to create a query that gives me back 11 records (one for every webpage record) with two columns: webpage_id, role_id. The role_id column should either be 20 or null.

    The resulting set should look like this:

    webpage_id role_id
    1 null
    2 null
    3 null
    4 null
    5 null
    6 20
    7 20
    8 null
    9 null
    10 null
    11 20

    I've tried creating a query using a left join and I get close but I end up with duplicate records.

    I can get the query to work in Access using two queries but I don't know how to translate it to mySql.

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    Houston, Texas, USA
    Posts
    559
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Here's the answer.

    Ok, I solved my own problem. Here's the query I came up with.

    select w.webpage_id, w.name page_name, w.menu_group, wp.role_id from webpages w
    left join webrole_permissions wp on (wp.webpage_id = w.webpage_id and wp.role_id = 20)
    order by w.menu_group, w.name


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
  •