I would like to delete the row with 0 (zeroes)

I honestly thought this would be easy.

Please see matrix below:

**Project           Resources            Hours**
TeamSite          Web Developer         30
RFP               Technical Writer      45
Db Conversion     DBA                   20
DB Conversion     DB Developer          25
DB Conversion     0                     0
DB Conversion     0                     0

For some reason, when we add data to the db, 2 extra records of DB Conversion is added. Resources and Hours have zeros added.

We would like to NOT show any rows where values for Resources and Hours are either null or 0 even though Project Has DB Conversion value.

Here is the code I am playing with but doesn't show me, for instance, the first 4 rows where Resources and Hours are not 0.

SELECT project, Hours, Reso开发者_如何学Curces
                         FROM mytable
                         inner join empTable ON myTable.employeeID = empTable.employeeID
                         and empTable.employeeID = '@employeeId' AND (Resources <> 0 and Hours <> 0)
 ORDER BY empTable.employeeID

I am either getting blank or one row. Please help.

I hope this isn't too much trouble.

Thanks a lot

I think that your problem is with the way you are using your parameter. It shouldn't be wrapped in '. Try this:

SELECT project, Hours, Resources
FROM mytable
inner join empTable ON myTable.employeeID = empTable.employeeID
WHERE empTable.employeeID = @employeeId AND (Resources <> 0 and Hours <> 0)
ORDER BY empTable.employeeID

You must find out why the extra rows are getting inserted. One approach is to put a constraint on the row so that the insert fails and work backwards from there.

I am either getting blank or one row. Please help.

As for your problem, try to break it into small parts:

1. Drop the inner join and non-zero constraints and ensure your able to select rows for the employeeId, including the bad rows. If not, your employeeId is missing

2. Add the constraints ('0' for Resources, not 0) and ensure you are able to select only good rows. If not either you did not write the predicate right or you have no good rows

3. Add the inner join and ensure same result. If not, there are no rows in master table. This is a bad thing, because you have project time for non-existent employees.

Assuming following tables structure: mytable(Projects,EmpID); empTable(empID,hours,resources);

Using alias e and m,is more optimized to get fast results.

SELECT m.project, e.Hours, e.Resources
FROM mytable m
     inner join 
     empTable e
     ON (
     m.employeeID = e.employeeID 
     AND 
     e.employeeID = @employeeId
     AND 
    (e.Resources <> '0' AND e.Hours>0)
    )

 ORDER BY e.employeeID

Try this:

SELECT project, Hours, Resources
FROM mytable
inner join empTable 
ON myTable.employeeID = empTable.employeeID
WHERE empTable.employeeID = @employeeId 
AND (Resources <> '0' and Hours > 0)  
ORDER BY empTable.employeeID 

Are you getting "one row"? Maybe it's the row corresponding to the empTable.employeeID. If you want 4 rows, try this:

SELECT project, Hours, Resources
FROM mytable
inner join empTable 
ON myTable.employeeID = empTable.employeeID
WHERE Resources <> '0' and Hours > 0
ORDER BY empTable.employeeID

I think you should use table name like you used in ORDER BY clause. This will help a lot.

SELECT table_name.project, table_name.Hours, table_name.Resources
                         FROM mytable
                         inner join empTable ON myTable.employeeID = empTable.employeeID
                         where table_name.Resources is not null and table_name.Hours is not null and table_name.Resources <> '0'
                         and table_name.Hours <> '0' ORDER BY empTable.employeeID