[racktables-users] help with sql needed
- From: Frank Altpeter <frank-racktables@xxxxxxxxxxx>
- To: racktables-users <racktables-users@xxxxxxxxxxxxx>
- Date: Thu, 20 Jan 2011 14:47:35 +0100
Hi list,
For internal service level management, I try to create an SQL statement
which doesn't work well and I don't have a clue how to solve it.
Given is the fact that I expanded the Chapter and Dictionary to have a
servicelevel field in object properties:
mysql> select * from Chapter where id = 10000;
+-------+--------+------------------+
| id | sticky | name |
+-------+--------+------------------+
| 10000 | no | Service Contract |
+-------+--------+------------------+
1 row in set (0.00 sec)
mysql> select * from Dictionary where chapter_id = 10000;
+------------+----------+----------------------------------+
| chapter_id | dict_key | dict_value |
+------------+----------+----------------------------------+
| 10000 | 50027 | BRONZE Service |
| 10000 | 50094 | Custom Service (Siehe Kommentar) |
| 10000 | 50024 | Full Service |
| 10000 | 50025 | GOLD Service |
| 10000 | 50059 | KEIN Service (Nach Aufwand) |
| 10000 | 50026 | SILBER Service |
+------------+----------+----------------------------------+
Additionally, I created a tag "Customers" with some sub-tags containing
the customer's IDs.
Now I want to fetch every server object which has a specific service
contract set, and the corresponding customer tag as well.
For the first part, this is fine with simple join, but adding the tag
output doesn't seem to work well. If an object has no tag, the object
isn't listed at all with my current query:
select tt.tag AS Kunde, ro.Name AS Hostname, d.dict_value AS ServiceLevel \
from TagTree tt, TagStorage ts, Dictionary d, RackObject ro,
AttributeValue av \
WHERE ro.id = av.object_id \
AND ts.entity_id = ro.id \
AND tt.parent_id = 6 \
AND ts.entity_realm = 'object' \
AND ts.tag_id = tt.id \
AND d.dict_key = av.uint_value \
AND av.attr_id = 10001 \
AND av.uint_value in ( 50025, 50026, 50027 ) \
ORDER BY tt.tag, av.uint_value, ro.Name;
I think I have to work with multiple JOIN statements here, but the
different approaches tested for now didn't work at all, so I hope to find
someone here who could give me a hint on that.
Mit freundlichen Grüßen
Frank Altpeter
--
FA-RIPE || http://www.altpeter.de/
Viva la evolution
Jetzt kostenlos bei XING anmelden: http://www.xing.com/go/invite/27666.2a971e
| Gentlemen, I want you to know that I am not always right, but I am
| never wrong. -Samuel Goldwyn
Other related posts:
