Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
miky001@yahoo.com
Starting Member
5 Posts |
 Posted - 2008-03-05 : 22:33:32
|
| Greetings!I have 11 tables with identical fields :HOSTS, OS, CLASSIFICATION, SITEThe 10 tables (Table 1, 2,..10) are meant for holding data regarding the compliance for a particular attribute. For example, all assets have to follow a naming convention, if they don't follow, they are recorded in Table 1. The same way, if they have any unacceptable os, they are recorded in table 2; and so on. There are 10 such attributes which are monitored in those respective 10 tables.The 11th table is a Master Table that has all the hosts which is the master inventory table.How do I write a query to get a list of all the hosts that belong to any of those 10 tables which could be appearing in 1 or more of those 10 table(s).Thanks in advanceMiky001 |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-05 : 22:37:48
|
Then union would work but I imagine there are better ways that i am unaware of.Select a.HostID FROM a.MasterTable INNER JOIN (Select HostID FROM TABLE1 UNION Select HostID FROM TABLE2 etc...) bon a.HostiD = b.HostIDMight get nasty if those 10 tables have a combined millions of rows
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
miky001@yahoo.com
Starting Member
5 Posts |
Posted - 2008-03-05 : 23:25:13
|
| Thanks for the prompt reply!I think the results get skewed because of the fact that host_name appears more than once in the master table. I am going to work on a unique identifier may be by combining 2 fields eg., host_name + OS. Is this possible?Thanks again! |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-05 : 23:33:53
|
Just add distinct keyword or add GROUP BY a.HOST_ID
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
|
|
Union or Subquery