[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

[ika]

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';

[Derrick]

Thank you for your help, this stuff does not look that hard, but at times i have trouble figuring certain things out.

Derrick

[Derrick]

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 |
+------------+------------+--------+-------+

[r937]

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/

[Derrick]

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

[r937]

um, that's an inner join

try one of these tutorials --

Getting the Right Data with SQL Joins
ANSI Joins
SQL Join

rudy

[Derrick]

thanks