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

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

Post by Don » Wed, 07 Aug 2002 04:31:49



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

 
 
 

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

Post by oj » Wed, 07 Aug 2002 04:43:10


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


 
 
 

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

Post by Mikhail Berlyan » Wed, 07 Aug 2002 04:39:35


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

 
 
 

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

Post by Don » Wed, 07 Aug 2002 05:03:54


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

 
 
 

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

Post by Steve Kas » Wed, 07 Aug 2002 05:16:16


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


> 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

 
 
 

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

Any tips on this (using MSSQL 2000)

Table_ID   Field1  Field2
1                4            5
6                4            3
3                5            8
2                6            8
4                6            8
5                6            2
7                7            1
9                9            8
10              9            8
12              9            2
11              9            2

The recordset is sorted by Field1.  Then we only want to count a row when
Field2 is not equal to the preceding record.  In this case, we would get:

Field2   CountOfField2
1            1
2            2
3            1
5            1
8            2

2. market share for database vendors

3. Table Record Counts do not Match

4. Need Help: Text Boxes & Data

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

6. Animated Flashlight in Windows

7. SQL Counting Records from another Table

8. How can this be done?

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

10. SQL Table record count?

11. combining records and determining record count

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

13. FMP5 Record Count of Found Record