SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question mysql query problem, can this be done in 1 querry?

    First of all, thank you guys for helping me the past few weeks. I've really been able to learn alot from this forum.

    Now onto the problem:

    The following script works, but I need to add another condtion:

    PHP Code:
    SELECT employee.idemployee.fnameemployee.lname  
    FROM employee  
    LEFT JOIN rat ON employee
    .id rat.empid  
    WHERE rat
    .empid IS NULL 
    I need to add the following condition:

    PHP Code:
    and rat.weekid between 1 and 
    for the querry on the rat table, any ideas?

    I though this would work, but it's a no go


    PHP Code:
    SELECT employee.idfnamelname  
    FROM employee  
    LEFT JOIN rat ON employee
    .id rat.empid  
    WHERE rat
    .empid IS NULL and rat.weekid between 1 and 
    Any ideas?
    The thought is I need all employees in the employee table not in the rat table, but only ones between rat.weekid 1 and 4.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it might be possible, but first please explain what "all employees in the employee table not in the rat table, but only ones between rat.weekid 1 and 4" means

    each employee is either in the rat table, or not

    does the weekid condition get ANDed or ORed? does it apply to employees that are in the rat table, or ones that aren't?

    rudy
    http://rudy.ca/

  3. #3
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    further explanation

    table format

    table.employee
    id, fname, lname

    table.rat
    id, empid, weekid

    I'm trying to get a list of employees for a specific week (week.id) that are not in the rat table.

    In other words: list all employees from the employee table and take out the list of employees from the rat table (rat.empid) where weekid has a specific value.

  4. #4
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Mar 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT employee.id, employee.fname, employee.lname  
    FROM employee  
    LEFT JOIN rat ON employee.id = rat.empid  
    WHERE rat.empid IS NULL
    this makes no sense at all. if rat.empid is null, then employee.id is gona be null. try this...

    Code:
    SELECT id,
           fname,
           lname
    FROM   employee
    WHERE  id NOT IN (SELECT empid
                      FROM   rat
                      WHERE  weekid BETWEEN 1 AND 3)

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    darkeye, he said it was a mysql problem, mysql don't do subselects

    Code:
    select id, fname, lname
      from employee
    left outer 
      join rat
        on employee.id = rat.empid
     where rat.id is null
        or not rat.weekid between 1 and 4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Mar 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    damn, thats what i thought! mysql blows (yes, bring on the flames ).

    are you sure this can be done with an table join? im having a little trouble wrapping my brain around your code, r937. i cant realy see it doing the same thing my query would (assuming you broke it up into two seprate queries to make it work with mysql).

  7. #7
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    it worked

    thanks, the following worked!

    Originally posted by r937
    darkeye, he said it was a mysql problem, mysql don't do subselects

    Code:
    select id, fname, lname
      from employee
    left outer 
      join rat
        on employee.id = rat.empid
     where rat.id is null
        or not rat.weekid between 1 and 4


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
  •