sql query help?

[dkol...@olearycomputers.com]

Hi;

I have a query that actually gets the results I'm looking for; however, it just seems like there should be a better way to do it.

I have UNIX host information defined in a number of tables. Table details are at the bottom.

I'm looking for information on any hosts that supports a business application (bappid) who's support
group is 'middleware' or who's technical app (tappid) is supported by 'middleware'.

The only way I could get this to work was through a union but that feels real kludgey. I'm hoping a
n expert can show me a more straight foward approach?

Any help gratefully accepted.

Thanks for your time.

Doug O'Leary

Query:

select hostname, status, dc, os, ver, c.atype, c.name, d.name
from hosts a, networks b, apps c, apps d
where a.netid = b.netid and dc = 'cl1' and
a.bappid = c.appid and c.sn_group = 'middleware' and
a.tappid = d.appid
union
select hostname, status, dc, os, ver, d.atype, c.name, d.name
from hosts a, networks b, apps c, apps d
where a.netid = b.netid and dc = 'cl1' and
a.tappid = d.appid and d.sn_group = 'middleware' and
a.bappid = c.appid
order by hostname

Tables:

mysql> desc hosts;
+----------+-------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------------------+------+-----+---------+----------------+
| hostid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| hostname | varchar(100) | NO | | NULL | |
| fqdn | varchar(100) | YES | | NULL | |
| ip | varchar(15) | YES | | NULL | |
| netid | int(10) unsigned | YES | MUL | NULL | |
[[ irrelevant columns snipped ]]
| env | varchar(25) | YES | | NULL | |
| os | varchar(25) | YES | | NULL | |
| ver | varchar(25) | YES | | NULL | |
| bappid | int(10) unsigned | YES | MUL | NULL | |
| tappid | int(10) unsigned | YES | MUL | NULL | |
+----------+-------------------------------+------+-----+---------+----------------+

mysql> desc networks;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| netid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| cidr | varchar(20) | NO | | NULL | |
| gw | varchar(15) | NO | | NULL | |
| dc | varchar(5) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+

mysql> desc apps;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| appid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| atype | enum('B','T') | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| sn_group | varchar(50) | YES | | NULL | |
+-----------+------------------+------+-----+---------+----------------+