Hi Guys,
I am new to SQL and I come across a problem that it is giving me some headaches. I hope some of you can point me to the right direction.
I have a table with a lot of information with different rows referring to the same object but with different values for different features. To explain better her is an example:
Index Value
1001 Interface1
1002 Interface2
1003 Interface3
1001 Up
1002 Up
1003 Down
1001 1
1002 10
1003 20
The first column is just an ID and the second a parameter of an interface. First three rows is just the name for each interface, the next three rows will represent the status of each interface and the last three rows represents the number of customers connected to that interface.
What I am trying to do is a query that gives me all the interfaces that are down and have more than 0 customers, which means that if an interface goes down but it doesn't have customers I don't care but if the interface is down with at least one customer that should show on the query
What I am struggling to achieve is a way of combining all this data into one virtual table just for the purpose of a query and identify the condition I mentioned before.
So virtually the table should show something like this
Index Value Value Value
1001 Interface1 Up 1
1002 Interface2 Up 10
1003 Interface3 Down 20
And after the proper condition applied I should be able to see just this:
1003 Interface3 Down 20
I have been playing around with 'Join' but for some reason I am not getting this right. I have something like this:
SELECT name.Index, name.Value, state.Value, clients.Value FROM table1 AS name
INNER JOIN table1 AS state ON (name.Index = state.Index)
INNER JOIN table1 AS clients ON (state.Index = clients.Index)
WHERE (state.Value = 'Down' AND clients.value > 0);
So I am trying to join columns from the same table but with different rows which doesn't seem to work. Can anyone give me an idea how to acomplish this?
Kind Regards
Luis