September 28, 2011 at 3:53 am
Hello.
Iam at the moment working on a projekt for at company, where I did make a MsSql database to contain data for different servers.
Basis
Many of the servers are working in pairs of (primary server and secondary server), but not all of them.
So I did make a table to contain all server data ofc.
Then I did make a Table to the Server_set.
Like the code below:
CREATE TABLE Server_set
server_set_id int IDENTITY(1,1) NOT NULL,
server_set_primary_id int NOT NULL,
server_set_secondary_id int NOT NULL,
both server_set_primary_id and server_set_secondary_id are foreign key to the Server table's primary key.
Table Server_set have some other columns which also are foreign keys to other tables.
My thought about Server_set is that I can SELECT a Server_set_id at get bolt servers in the server_Set with a INNER JOIN.
like this in a VIEW.
SELECT dbo.Hosts.host_name, dbo.Hosts.host_ip, dbo.Server_set.server_set_id, dbo.System.system_name
FROM dbo.Server_set INNER JOIN
dbo.System ON dbo.Server_set.server_set_system_id = dbo.System.system_id INNER JOIN
dbo.Hosts ON dbo.Server_set.server_set_primary_id = dbo.Hosts.host_id AND dbo.Server_set.server_set_secondary_id = dbo.Hosts.host_id
But it will not return any data, because of the AND between dbo.Hosts ON dbo.Server_set.server_set_primary_id = dbo.Hosts.host_id AND dbo.Server_set.server_set_secondary_id = dbo.Hosts.host_id
and it ofcause both foreign key never "point" to the same Server_id (called host in my setup)
I have try to make the INNER JOIN with OR, then I get my data, but 2 times for each Server_set_id.
I think it my desgin which could be better, but Iam not sure how.
hope some for you can point me in the right direction:
Iam using Microsoft SQL Management Studio to work with the DB.
//Kasper
September 28, 2011 at 8:14 am
Before anybody can possibly make any suggestions on your data structures we have to know what they are. Can you post your ddl for the tables in question?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2011 at 8:49 am
le fevre (9/28/2011)
Hello.Iam at the moment working on a projekt for at company, where I did make a MsSql database to contain data for different servers.
Basis
Many of the servers are working in pairs of (primary server and secondary server), but not all of them.
So I did make a table to contain all server data ofc.
Then I did make a Table to the Server_set.
Like the code below:
CREATE TABLE Server_set
server_set_id int IDENTITY(1,1) NOT NULL,
server_set_primary_id int NOT NULL,
server_set_secondary_id int NOT NULL,
both server_set_primary_id and server_set_secondary_id are foreign key to the Server table's primary key.
Table Server_set have some other columns which also are foreign keys to other tables.
My thought about Server_set is that I can SELECT a Server_set_id at get bolt servers in the server_Set with a INNER JOIN.
like this in a VIEW.
SELECT dbo.Hosts.host_name, dbo.Hosts.host_ip, dbo.Server_set.server_set_id, dbo.System.system_name
FROM dbo.Server_set INNER JOIN
dbo.System ON dbo.Server_set.server_set_system_id = dbo.System.system_id INNER JOIN
dbo.Hosts ON dbo.Server_set.server_set_primary_id = dbo.Hosts.host_id AND dbo.Server_set.server_set_secondary_id = dbo.Hosts.host_id
But it will not return any data, because of the AND between dbo.Hosts ON dbo.Server_set.server_set_primary_id = dbo.Hosts.host_id AND dbo.Server_set.server_set_secondary_id = dbo.Hosts.host_id
and it ofcause both foreign key never "point" to the same Server_id (called host in my setup)
I have try to make the INNER JOIN with OR, then I get my data, but 2 times for each Server_set_id.
I think it my desgin which could be better, but Iam not sure how.
hope some for you can point me in the right direction:
Iam using Microsoft SQL Management Studio to work with the DB.
//Kasper
I believe you can use some left joins to do what you are trying to do.
SELECT isnull(h1.host_name, h2.Host_name), isnull(h1.host_ip,h2.host_ip), ss.server_set_id, s.system_name
FROM dbo.Server_set ss
INNER JOIN dbo.System s ON ss.server_set_system_id = s.system_id
left JOIN dbo.Hosts h1 ON ss.server_set_primary_id = h1.host_id
left JOIN dbo.Hosts h2 ON ss.server_set_secondary_id = h2.host_id
So if the left join doesn't match it returns null. So if the first left join isnull then we try the second left join. If it is also null then you will get null.
Hard to know for sure if this is correct, but I am guessing something like this should work for you.
Ben
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply