Counting Records in Table not Connected to Records in Another (SQL Newbie) 
Author Message
 Counting Records in Table not Connected to Records in Another (SQL Newbie)

I have a table of positions and a table of employees.  In the employees
table is a position number which corresponds to the position number in the
positions table.  Up to 2 employees can have the same position number.  Is
there a slick way in SQL to count the number of positions which do not have
an employee assigned?

employee.position
    1
    2
    3
    2
    1
    5

position.number
    1
    2
    3
    4
    5

Vacant positions = 1  (position_number 4)

Any suggestions or references will be greatly appreciated!!

Thanks!!

Don



Sat, 22 Jan 2005 03:31:49 GMT
 Counting Records in Table not Connected to Records in Another (SQL Newbie)

don,

select number
from position
where not exists(select *
from employee
where employee.position=position.number)

--
-oj
Rac v2.1 Public Beta is RELEASED.
http://www.rac4sql.net


Quote:
> I have a table of positions and a table of employees.  In the employees
> table is a position number which corresponds to the position number in the
> positions table.  Up to 2 employees can have the same position number.  Is
> there a slick way in SQL to count the number of positions which do not
have
> an employee assigned?

> employee.position
>     1
>     2
>     3
>     2
>     1
>     5

> position.number
>     1
>     2
>     3
>     4
>     5

> Vacant positions = 1  (position_number 4)

> Any suggestions or references will be greatly appreciated!!

> Thanks!!

> Don



Sat, 22 Jan 2005 03:43:10 GMT
 Counting Records in Table not Connected to Records in Another (SQL Newbie)
select count(*) from TableOfPositions
where not number in (select position from TableOfEmployee)

Mikhail Berlyant
Data Integrator, Data Systems
Launch Your Yahoo!Music Experience  http://launch.yahoo.com
Brainbench MVP for Visual Basic   www.brainbench.com


Quote:
> I have a table of positions and a table of employees.  In the employees
> table is a position number which corresponds to the position number in the
> positions table.  Up to 2 employees can have the same position number.  Is
> there a slick way in SQL to count the number of positions which do not
have
> an employee assigned?

> employee.position
>     1
>     2
>     3
>     2
>     1
>     5

> position.number
>     1
>     2
>     3
>     4
>     5

> Vacant positions = 1  (position_number 4)

> Any suggestions or references will be greatly appreciated!!

> Thanks!!

> Don



Sat, 22 Jan 2005 03:39:35 GMT
 Counting Records in Table not Connected to Records in Another (SQL Newbie)
Just exactly the hint I needed!!

Thank you very much!!!

Don


Quote:
> I have a table of positions and a table of employees.  In the employees
> table is a position number which corresponds to the position number in the
> positions table.  Up to 2 employees can have the same position number.  Is
> there a slick way in SQL to count the number of positions which do not
have
> an employee assigned?

> employee.position
>     1
>     2
>     3
>     2
>     1
>     5

> position.number
>     1
>     2
>     3
>     4
>     5

> Vacant positions = 1  (position_number 4)

> Any suggestions or references will be greatly appreciated!!

> Thanks!!

> Don



Sat, 22 Jan 2005 04:03:54 GMT
 Counting Records in Table not Connected to Records in Another (SQL Newbie)
If employee.position is a foreign key, then you can do this
without needing to actually match up the tables:

select
  (select count(distinct position) from employee)
- (select count(number) from position)

Steve Kass
Drew University

Quote:

> I have a table of positions and a table of employees.  In the employees
> table is a position number which corresponds to the position number in the
> positions table.  Up to 2 employees can have the same position number.  Is
> there a slick way in SQL to count the number of positions which do not have
> an employee assigned?

> employee.position
>     1
>     2
>     3
>     2
>     1
>     5

> position.number
>     1
>     2
>     3
>     4
>     5

> Vacant positions = 1  (position_number 4)

> Any suggestions or references will be greatly appreciated!!

> Thanks!!

> Don



Sat, 22 Jan 2005 04:16:16 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. counting record only when field value is not equal to value of previous record

2. Table Record Counts do not Match

3. Lots records not being posted in table, identity_column shows missing records

4. SQL Counting Records from another Table

5. Counting specific records to SQL table ( Need Help!!)

6. SQL Table record count?

7. combining records and determining record count

8. counting records only when value is different than previous record (MSSQL 2000)

9. FMP5 Record Count of Found Record

10. Counting Records (newbie)

11. NEWBIE - Record Count Help

12. 1 table for many records or many tables with few records


 
Powered by phpBB® Forum Software