SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 40 of 40
  1. #26
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you make either of the changes I suggested? It looks to me at first glance that all you need to do is SELECT u_all.username instead of SELECT u_one.username as you currently are.
    PHP questions? RTFM
    MySQL questions? RTFM

  2. #27
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That doesn't work. That just shows the supervisor account and I need to see the users not him.

  3. #28
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The following code shows all the users that have a department similar to the supervisor.

    PHP Code:
    SELECT FROM authorize AS u_one INNER JOIN authorize AS u_all ON u_all.department IN u_one.department u_one.department2 u_one.department3 u_one.department4 u_one.department5 u_one.department6 WHERE u_one.username '$_SESSION[user_name]' 
    I need to take it a step farther and say not only do they a department that matches the user.. but it also needs to have the column lockedstatus set to lock. That's all I need to do.

  4. #29
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wickedneat View Post
    What I need is to be able to display the users of those why have the locked status, and one of there departments are matching up with alteast one of the supervisors departments
    for a moment, this sentence had promise, because it states your requirement

    presumably "person who is to see the outcome" is the session user?

    that's as far as i got

    users that have the locked status, that's easy, it's a WHERE condition on the Workers table

    hey, where did the Workers table come from?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #30
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes the person who is Session user is the supervisor he needs to be able to see all the users. basically the users it shows in the existing select statement are the right ones, I just need it to eliminate all those users who don't have lockedstatus set to locked

  6. #31
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    and what happened to the timesheet table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #32
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't need that table anymore. I got around having to use that with the lockedstatus column

  8. #33
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this, although as Rudy suggested you'll have a much easier time with pretty much everything if you normalize your tables.
    Code:
    SELECT u_all.username
      FROM authorize AS u_one 
    INNER 
      JOIN authorize AS u_all 
        ON u_all.department IN ( u_one.department 
                               , u_one.department2 
                               , u_one.department3 
                               , u_one.department4 
                               , u_one.department5 
                               , u_one.department6 ) 
     WHERE u_one.username = '{$_SESSION['user_name']}'
        AND lockedstatus = 'LOCKED'
        AND u_all.username <> '{$_SESSION['user_name']}'
    PHP questions? RTFM
    MySQL questions? RTFM

  9. #34
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wickedneat View Post
    I just need it to eliminate all those users who don't have lockedstatus set to locked
    ... AND NOT u_all.lockedstatus = 'LOCKED'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #35
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there is no workers or supervisor tables those users are all in the authorize table.

  11. #36
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wickedneat View Post
    I don't need that table anymore.
    sounds like you are still in a position to change your design

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

  12. #37
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks R937, that's what I was looking for except AND NOT u_all.lockedstatus = 'UNLOCKED'

    This works great thanks man.

    I think you won the award for "Overcoming Wicked's Idiotic Misconceptions Award"

  13. #38
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the only reason i used the table aliases "u_one" and "u_all" was because of your wording in the previous thread, where we came up with that join

    you should at least write your query with more appropriate table aliases

    otherwise two weeks from now, you'll want to do something similar, or make changes to this query, and you won't have a clue as to how it really works

    try like this --
    Code:
    SELECT worker.username
      FROM authorize AS supervisor 
    INNER 
      JOIN authorize AS worker 
        ON worker.department IN ( supervisor.department 
                                , supervisor.department2 
                                , supervisor.department3 
                                , supervisor.department4 
                                , supervisor.department5 
                                , supervisor.department6 ) 
       AND NOT worker.username = supervisor.username
       AND NOT worker.lockedstatus = 'LOCKED'
     WHERE supervisor.username = '{$_SESSION['user_name']}'
    props to kromey for realizing that you don't want to return the supervisor himself in the join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #39
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What do you mean when you keep saying I need to normalize my tables?

  15. #40
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wickedneat View Post
    What do you mean when you keep saying I need to normalize my tables?
    sorry, it sounds like a fifty-cent word, i know, but it really is the best word to describe how your table could be improved

    do a search, you're in for a surprise
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •