SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    In a house in the USA
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Using an AS variable in WHERE

    I have a query that pulls all users from a database for a phone listing. I have one table that is all of the users with UserID as the Primary Key(PK). Then I have a cell phones table that has CellID as the PK. Then a cellphone relationship table with a PK of UserID,CellID.

    *I am using PHP5 and MySQL5

    The query:
    Code:
    CREATE VIEW phonelisting as select
      users.UserID,
      concat_ws(' ', users.fname, users.minitial, users.lname) AS userName,
      users.positionTitle AS positionTitle,
      users.deskPhone,
      users.DID,
      users.homePhone,
      users.pager,
      group_concat(distinct concat(substr(cell.number, 1, 3), '-', substr(cell.number,4 , 4)) order by cell.number ASC separator '<br />') AS cellphones
    FROM
      users
    LEFT JOIN cellrelationship
      ON
        cellrelationship.userID = users.UserID
    LEFT JOIN cell 
      ON
        cell.CellID = cellrelationship.cellID && cell.hideListing <> '1'
    WHERE
      users.userStatus <> 'term' && users.hideListing <> '1' && (users.termdate = '' OR users.termdate > UNIX_TIMESTAMP(CURRENT_DATE)) && (users.deskPhone <> '' OR users.pager <> '')
    GROUP BY
      users.UserID
    ORDER BY
      concat_ws(' ', users.fname, users.minitial, users.lname)
    What I would like to do is use the cellphones value in the SELECT part of the query in the WHERE clause.
    The query should pull all users that:
    1. Are not terminated (Signified by users.userStatus <> 'term')
    2. Users that do not have a termination date or that the termination date has yet to come (Signified by users.termdate)
    3. Users that have at least a cell phone, a pager, or a desk phone
    4. Then order the users by fname minitial lname

    The part that I have yet to get to work is if they have a cell phone or not. Any suggestions?
    Daniel
    http://www.wlscripting.com - PHP Tutorials and code snippets
    Notepad++ Function List plugin tip - for PHP developers

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    NL, Rotterdam
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe I'm overlooking something but wouldn't changing the 'Left Join cell' into an Inner Join do that? Another way would be to check for the value of Cell.CellId.

    Code MySQL:
    SELECT
    ...
    IFNULL(cell.CellID,0)
    ...
    JOIN JOIN JOIN
    ...
    WHERE 
    cell.CellID>0

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    CREATE VIEW phonelisting 
    AS 
    SELECT users.UserID
         , CONCAT_WS(' '
                    , users.fname
                    , users.minitial
                    , users.lname) AS userName
         , users.positionTitle 
         , users.deskPhone
         , users.DID
         , users.homePhone
         , users.pager
         , cells.cellphones
      FROM users
    LEFT 
      JOIN (
           SELECT cellrelationship.userID
                , GROUP_CONCAT( distinct 
                    CONCAT( SUBSTR(cell.number,1,3)
                          , '-'
                          , SUBSTR(cell.number,4,4)
                          ) 
                    ORDER BY cell.number ASC 
                    SEPARATOR '<br />') AS cellphones
             FROM cell
           INNER
             JOIN cellrelationship
               ON cellrelationship.CellID = cell.cellID 
            WHERE cell.hideListing <> '1'
           GROUP
               BY cellrelationship.userID
           ) AS cells
        ON cells.userID = users.UserID
     WHERE users.userStatus <> 'term' 
       AND users.hideListing <> '1' 
       AND ( users.termdate = '' 
          OR users.termdate > UNIX_TIMESTAMP(CURRENT_DATE)
           ) 
       AND ( users.deskPhone <> '' 
          OR users.pager <> ''
           )
    ORDER 
        BY userName
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    In a house in the USA
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937! That does what I was looking for. I never knew about being able to create a join like that using a select statement.
    Daniel
    http://www.wlscripting.com - PHP Tutorials and code snippets
    Notepad++ Function List plugin tip - for PHP developers

  5. #5
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    In a house in the USA
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, When you do a query in the JOIN instead of creating the query like I had originally posted. Does this save any resources, is it faster? I have a query like the above that ends up linking a half dozen tables together to link cell phones, desktop workstations, laptops and laptop printers all together just like I am joining cell phones above but it returns all users even if no "resources" are provided to them.
    Daniel
    http://www.wlscripting.com - PHP Tutorials and code snippets
    Notepad++ Function List plugin tip - for PHP developers

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WLHosting View Post
    r937, When you do a query in the JOIN instead of creating the query like I had originally posted. Does this save any resources, is it faster?
    it has the potential to be much faster

    the general rule is "push GROUP BYs down to the deepest subquery"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    In a house in the USA
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, just so you know. I found that the query runs perfectly but when you create a view with that query in MySQL 5 you get an error: " No subquery in the FROM clause".

    There is a bug fix / feature request listed at: http://bugs.mysql.com/bug.php?id=16757 for this problem. I have since found a way to work around this using a very similar query as to what I first posted. But you did show me something I did not know about.
    Daniel
    http://www.wlscripting.com - PHP Tutorials and code snippets
    Notepad++ Function List plugin tip - for PHP developers


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
  •