Looking for an SQL statement which groups by type

first, I was pretty lost giving this question a correct title.

I'm working on a system which allows me to find specific networking devices. A network device (called "system" in my example) has a number of ports, where each port can have a specific configuration. An example would be: Return all devices which have at least 2 ports of type 100BASE-TX and at least 1 port of 1000BASE-TX.

Here's my example table which is named "ports":

system  port  type
1       1     10BASE-T
1       1     100BASE-TX
1       1     1000BASE-TX
1       2     10BASE-T
1       2     100BASE-TX
1       2     1000BASE-TX
1       3     10BASE-T
1       3     100BASE-TX
1       3     1000BASE-TX
2       1     100BASE-TX
2       2     100BASE-TX
2       3     100BASE-TX

Column descriptions: "system" is the ID of the system which contains the ports "port" is the ID of the port "type" is the type which that single port can have

I'm pretty lost here, and I don't ask for a complete query, maybe some hints are enough for me to figure out the rest. I already tried to join the table with itself to retrieve all possible port combinations, but from that point I was lost again.

Here's my pseudo-code:

SELECT system FROM ports WHERE (number-of-possible-100base-tx-ports >= 2 AND number-of-possible-1000base-tx-ports >= 1)

Here's my expec开发者_StackOverflow社区ted result:

system
1

It is important to know that a port can be either of one or another type. Basically I want the user to ask: "List all devices which support 2 100BASE-TX ports and at least 1 1000BASE-TX port at the same time". For example, the following pseudo-sql should not return any results:

SELECT system FROM ports WHERE (number-of-possible-100base-tx-ports >= 2 AND number-of-possible-1000base-tx-ports >= 2)

This query shouldn't return any result since no device has more than three ports overall.

EDIT

Here's another pseudo-SQL which represent the question better:

SELECT system FROM ports WHERE (at-least-1-type = 1000BASE-TX AND at-least-2-other-types = 100BASE-TX) AND portid-from-type-1000BASE-TX <> portid-from-type-100BASE-TX

EDIT #2

After one night, I realized that it might not be possible using plain SQL. What I need would be an intermediate table containing all possible configurations per system, and I believe that table would be quite huge. Given the example table above, I would already have 27 different combinations for system 1; regular networking devices have 12, 24 or 48 ports and storing all combinations in a database wouldn't be very efficient. I have to think of a programmatic way to solve this problem.

Thanks in advance! Timo

I've had a bash at this using SQLite and this query seems to work ok for the limited test data I've tested it against.

select sys as system from (
  select a.sys, count(distinct a.port) as want_a, count(distinct b.port) as want_b
  from test a left join test b
  on a.sys=b.sys and a.port<>b.port and a.type<>b.type
  where
       a.type='$type_a'
   and (b.type='$type_b' or b.type is null)
   and a.sys in (
     select sys from test group by sys having count(distinct port) >= $want_a+$want_b
   )
  group by a.sys
  having want_a >= $want_a and want_b >= $want_b
) z;

Where $want_a is the count of ports for $type_a and $want_b is the count for $type_b. So your initial query up there has want_a=2, type_a='100BASE-TX', want_b=1, type_b='1000BASE-TX'.

In the gist, the first file is mysql.sh (test driver script, ./mysql.sh < test.txt), second is test.txt (test data), third is gotest.sh (sqlite3 driver script, ./gotest.sh < test.txt), third is the SQL. All tests PASS in mysql and sqlite3 so that's promising.

I think you need to clarify this requirement a bit further:

Return all devices which have at least 2 ports of type 100BASE-TX and at least 1 port of 1000BASE-TX.

Since a single port can have more than one type, would this device satisfy the query or not?

port    type
1       100BASE-TX
1       1000BASE-TX
2       100BASE-TX

Taking your requirement literally, I think this device qualifies, but I suspect what you really want is a device which can support 2 100BASE-TX and 1 1000BASE-TX connections at the same time, so would need to have at least 3 ports.

The answer here is to normalize the data.

Table 1:
System ID - key
Description

Table 2:
Port Type ID - key
Description

Table 3:
System ID - Key
Port ID - Key
Port Type

Select count(*), port_type from table_1 a, table_3 c
where a.system_id=c.system_id
group by port_type
having count(*) > 2

I hope that gets you close.

Well, first i might question this table structure... but from your description of the system you're working on it may be difficult to change...

I would suggest a query with two sub-queries like this:

SELCT * 
FROM 
    (
        SELECT COUNT(port) AS Port1Count, system 
        FROM ports 
        WHERE type = '100BASE-TX' 
            AND Port1Count >= 2 
        GROUP BY system
    ) AS A 
INNER JOIN 
   (
        SELECT COUNT(port) AS Port2Count, system 
        FROM ports 
        WHERE type = '1000BASE-TX' 
            AND Port2Count >= 1 
        GROUP BY system
    ) AS B ON A.System = B.System

This may not be EXACT depending on the flavor of SQL and I may have some syntax wrong here or there since I didn't actually try building your table. Hope it helps!

After one night, I realized that it might not be possible using plain SQL. What I need would be an intermediate table containing all possible configurations per system, and I believe that table would be quite huge. Given the example table above, I would already have 27 different combinations for system 1; regular networking devices have 12, 24 or 48 ports and storing all combinations in a database wouldn't be very efficient. I have to think of a programmatic way to solve this problem.