SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Query Problems

  1. #1
    SitePoint Member
    Join Date
    Mar 2004
    Location
    College Station
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query Problems

    Query Problems

    Hi! I've been having some problems with a query for a while and I just can't seem to figure it out.

    I need to combine the following queries into one (below). Basically, I have two tables and I would like to combine them into one for output into html. The two tables are `categories` and `permissions`. I would like all of the categories to show and just some of the permissions (the ones that pertain) but want it to show null in the array if there aren't permissions for this user.

    Here are the two queries in question:

    Query 1 -
    SELECT categories_id, menuname, ordering FROM categories ORDER BY ordering ASC

    Query 2 -
    SELECT permissions_id, user_id, category_id FROM permissions WHERE user_id = 12

    Here's the output I'm looking for:
    Code:
    +----------------+------------+------------+------------------+----------+--------------+
    | categories_id  | menuname   |  ordering  |  permissions_id  | user_id  | category_id  |
    +----------------+------------+------------+------------------+----------+--------------+
    |       1        |   Admin    |     1      |      1           |   12     |      1       |
    |       2        |   Jobs     |     2      |      2           |   12     |      2       |
    |       3        |   About Us |     3      |      NULL        |   NULL   |      NULL    |
    +----------------+------------+------------+------------------+----------+--------------+
    Can anybody help? I've tried LEFT JOIN, but it only pulls up the ones with the user_id in it.

    Oh and here's the platform, Apache 2, PHP 4.2, MySQL 3.23

    Thank you in advance!!
    Chris

  2. #2
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Australia
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT c.categories_id, c.menuname, c.ordering, p.permissions_id, p.user_id, p.category_id
    FROM categories c
    JOIN permissions p
    on c.categories_id = p.category_id
    and p.user_id = 12
    ORDER BY c.ordering ASC


    Try that one out

  3. #3
    SitePoint Member
    Join Date
    Mar 2004
    Location
    College Station
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Good

    Ok, I tried it as is, but it kept giving me an error with the SQL code, so I played with it a bit and here's what worked:

    Code:
    SELECT c.categories_id, c.menuname, c.ordering, p.permissions_id, p.user_id, p.category_id
    FROM categories c
    LEFT JOIN permissions p
    on c.categories_id = p.category_id
    and p.user_id = 12
    ORDER BY c.ordering ASC
    I just had to add "LEFT" in front of JOIN.

    By the way, I noticed you put "c" and "p" in front of the field names. I tried it with and without and it worked the same. Is there a reason to put that in there? Like, is it shorthand for putting the actual table name in front of the field name?

    Thank you very much for the help!!

    Chris

  4. #4
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Australia
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The c and p area alias's.

    In general you would have a link on the 2 tables which would have been the categories_id column but u named it differently on the 2nd table.

    If it was the same, and you were joining 2 tables, there would be an error as it wouldnt know what table you wanted to select the categories_id from.

    So instead of saying tablename.categories_id you have t.categories_id where t is defined after the table name, ie c or p in yur case

    Cheers,
    I was working Based on knowledge of MSSQL so left join is what i use wasnt t1005 sure if itt woked in mySQL but LEFT JOIN works, kewl.


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
  •