Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Unanswered: newbie to mysql stuck with a command

    I am trying to self teach mysql to myself through a tutorial and I am stuck with a command.

    i have this table:

    +------------+------------+--------+-------+
    | PROJECT_ID | CONSULTANT | DATE | HOURS |
    +------------+------------+--------+-------+
    | P01 | SUE | 910626 | 5.0 |
    | P01 | SUE | 910628 | 3.0 |
    | P03 | TED | 910713 | 6.0 |
    | P01 | URI | 910630 | 4.0 |
    | P03 | TED | 910715 | 7.0 |
    | P05 | TED | 910622 | 1.0 |
    | P03 | RAY | 910715 | 4.0 |
    | P05 | TED | 910624 | 3.0 |
    +------------+------------+--------+-------+

    I am trying to display all the entries in the table except for those by consultant SUE.

    I thought it might be something like:

    SELECT * FROM consultants WHERE CONSULTANT not like SUE;

    but it isnt working.

    Thanks for reading this,
    Derrick

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: newbie to mysql stuck with a command

    Originally posted by Derrick
    I am trying to self teach mysql to myself through a tutorial and I am stuck with a command.

    i have this table:

    +------------+------------+--------+-------+
    | PROJECT_ID | CONSULTANT | DATE | HOURS |
    +------------+------------+--------+-------+
    | P01 | SUE | 910626 | 5.0 |
    | P01 | SUE | 910628 | 3.0 |
    | P03 | TED | 910713 | 6.0 |
    | P01 | URI | 910630 | 4.0 |
    | P03 | TED | 910715 | 7.0 |
    | P05 | TED | 910622 | 1.0 |
    | P03 | RAY | 910715 | 4.0 |
    | P05 | TED | 910624 | 3.0 |
    +------------+------------+--------+-------+

    I am trying to display all the entries in the table except for those by consultant SUE.

    I thought it might be something like:

    SELECT * FROM consultants WHERE CONSULTANT not like SUE;

    but it isnt working.

    Thanks for reading this,
    Derrick
    SELECT * FROM consultants WHERE CONSULTANT <> 'SUE';
    (this is better)

    SELECT * FROM consultants WHERE CONSULTANT not like 'SUE';

  3. #3
    Join Date
    Nov 2003
    Posts
    6
    Thank you for your help, this stuff does not look that hard, but at times i have trouble figuring certain things out.

    Derrick

  4. #4
    Join Date
    Nov 2003
    Posts
    6
    Im stuck on another section

    Im trying to find the consultant_name and skill_id for all consultants with more than 7 hours for any given project.

    this is what i thought it was(clearly you can see i am really mized up here)

    SELECT consultant_name, skill_id
    FROM consultants
    WHERE HOURS IN
    (SELECT HOURS
    FROM time
    GROUP BY CONSULTANT
    WHERE SUM(HOURS) > '7');

    Please give me some advice

    Thanks,
    Derrick

    Consultants:
    +------------+-----------------+----------+--------+
    | CONSULTANT | CONSULTANT_NAME | SKILL_ID | REGION |
    +------------+-----------------+----------+--------+
    | RAY | Smith | PR | W |
    | SUE | Jones | AN | NE |
    | TED | Doe | DD | NE |
    | URI | Roe | CP | S |
    +------------+-----------------+----------+--------+

    Time:
    +------------+------------+--------+-------+
    | PROJECT_ID | CONSULTANT | DATE | HOURS |
    +------------+------------+--------+-------+
    | P01 | SUE | 910626 | 5.0 |
    | P01 | SUE | 910628 | 3.0 |
    | P03 | TED | 910713 | 6.0 |
    | P01 | URI | 910630 | 4.0 |
    | P03 | TED | 910715 | 7.0 |
    | P05 | TED | 910622 | 1.0 |
    | P03 | RAY | 910715 | 4.0 |
    | P05 | TED | 910624 | 3.0 |
    +------------+------------+--------+-------+

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select C.consultant_name
         , C.skill_id
         , T.project_id
         , sum(T.hours)
      from consultants C
    inner
      join `time` T
        on C.consultant = T.consultant  
    group 
        by C.consultant_name
         , C.skill_id
         , T.project_id
    having sum(T.hours) > 7
    rudy
    http://r937.com/

  6. #6
    Join Date
    Nov 2003
    Posts
    6
    im a little confused on this section here, could someone explain it to me?

    from consultants C
    inner
    join `time` T
    on C.consultant = T.consultant

    thanks,
    Derrick

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um, that's an inner join

    try one of these tutorials --

    Getting the Right Data with SQL Joins
    ANSI Joins
    SQL Join

    rudy

  8. #8
    Join Date
    Nov 2003
    Posts
    6
    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •