SQL query help

From: Bob McConnell <bobmc_at_cyberramp.net>
Date: Mon, 20 Apr 1998 16:32:38 GMT
Message-ID: <353b76d2.239318992@news.internetmci.com>

 Looking for help with a query.

  I have a table containing communications equipment descriptions with a layout like:

 TABLE1
city1 city2 equipment1 equipment2 network_type network_level

ABC   DEF    TYPE1       TYPE1             X                        99
ABC   DEF    TYPE1       TYPE1             X                      200
ABC   DEF    TYPE1       TYPE2             Y                        99
ABC   DEF    TYPE1       TYPE2             Y                      200
ABC   DEF    TYPE3       TYPE1             X                        99
ABC   DEF    TYPE3       TYPE1             X                      200
ABC   DEF    TYPE2       TYPE2             Y                        99
ABC   DEF    TYPE2       TYPE2             Y                      150

Table 2 contains a rating field base on the equipment
        selected from table 1

TABLE2
EQUIPMENT1 EQUIPMENT2 TYPE LEVEL RATING

TYPE1                     TYPE1          X         99     0
TYPE1                     TYPE1          Y         99     100
TYPE1                     TYPE2          Y          99     200
TYPE1                     TYPE1          X        200    50
TYPE2                     TYPE2          X        200    500
TYPE3                     TYPE1          X        200    50
TYPE3                      TYPE1         X          99     75

Table 3 also contains a rating field base on the
        network level selectd in table 1

TABLE3
REQUESTED_EVEL     ACTUAL_LEVEL RATING
99                                                  99	             0
99                                                200               10
99                                                150               15

The query I'm trying to develop is to
Select t1.equipment1, t1.equipment2, t1.network_type, t1.network_level,t2.rating+t3.rating
from table1 t1, table2 t1, table3 t3
where t1.city1 = ABC and t1.city2 = DEF and t1.network_level = 99 (USER INPUT FIELDS)

and t1.equipment1 = t2.equipment1
and t2.equipment2 = t2.equipment2
and t1.network_type = t2.type
and t1.network_level = t2.level
and t3.requested_network_level = t1.network_level

order by t2.rating+t3.rating ;

I'm trying to get all the records in table 1 which match the cities and network level which are the input parameters  and order the result set
by the sum of the ratings from table2 and 3.

thanks for any help
Bob

bob.mcconnell_at_mci.com
or
bobmc_at_cyberramp.net Received on Mon Apr 20 1998 - 11:32:38 CDT