SELECT query help

[SELECT query help]

Hi people,

Pls help to select the following data.
Tha table has subnet, address, serail# and model columns.
I need to select the data where various addresses belong
to the same subnet. See, a network subnet could be on the
same address. Sometimes, various addresses share the same
subnet. I would like to see the report where various
addresses share the same subnet, inluding serial and model
fields.
example:
subnet 1 address 1 serial 1 model 1
subnet 1 address 1 serial 2 model 1
subnet 1 address 2 serial 3 model 1
subnet 1 address 4 serial 4 model 1

Need to select this:
subnet 1 address 2 serial 3 model 1
subnet 1 address 4 serial 4 model 1

Thanks a million,

Serg

 

[John Spencer (MVP)]

I am sorry, but I don't understand your criteria for eliminating the first two
rows of the sample data. They seem to share the same subnet as the last two
rows.

What would you expect to see if Row 1 was eliminated from the record set? All
three remaining rows?

What would you expect to see if the additional row below was added?
subnet 1 address 2 serial 5 model 1

 

[Guest]

Thank you John,
The concern is to show a subnet where address is not the
same. I would say that the query has to return this:
subnet 1 address 1 serial 1 model 1
subnet 1 address 1 serial 2 model 1
subnet 1 address 2 serial 3 model 1
subnet 1 address 4 serial 4 model 1
from the table that contains this:
subnet 1 address 1 serial 1 model 1
subnet 1 address 1 serial 2 model 1
subnet 1 address 2 serial 3 model 1
subnet 1 address 4 serial 4 model 1
subnet 2 address 3 serial 6 model 1
subnet 2 address 3 serial 5 model 1
subnet 2 address 3 serial 7 model 1
Can you do this?
Thank you,
Serg

 

[John Spencer (MVP)]

OK, so if there is more than one address in a subnet, return all the records for
that subnet. So basically you need a Distinct count of the subnets plus address.

The simplest way to do this, is probably to chain some queries together.

QueryA:
SELECT DISTINCT SubNet, Address
FROM Table as A

QueryB:
SELECT B.SubNet, B.Address
FROM Table as B
WHERE B.Subnet IN
(SELECT A.Subnet
FROM QueryA as A
GROUP BY A.Subnet
HAVING COUNT(*) >1)

You can do this all in one query, but it is harder to understand and is only
possible if there are no spaces or special characters in your table and fieldnames.

 

[Serg]

Thank you John,
I will try to run the query....

-----Original Message-----
OK, so if there is more than one address in a subnet, return all the records for
that subnet. So basically you need a Distinct count of the subnets plus address.

The simplest way to do this, is probably to chain some queries together.

QueryA:
SELECT DISTINCT SubNet, Address
FROM Table as A

QueryB:
SELECT B.SubNet, B.Address
FROM Table as B
WHERE B.Subnet IN
(SELECT A.Subnet
FROM QueryA as A
GROUP BY A.Subnet
HAVING COUNT(*) >1)

You can do this all in one query, but it is harder to understand and is only
possible if there are no spaces or special characters in your table and fieldnames.




.