[racktables-users] Re: SQL query question: Multiple attribute values with one query
- From: "Jonathan Thurman" <jthurman42@xxxxxxxxx>
- To: racktables-users@xxxxxxxxxxxxx
- Date: Tue, 17 Jun 2008 21:45:05 -0700
This seems to work for me, although I don't have a lot of servers in
my test database. I broke it down onto multiple lines for easier
reading, but you can glob it all together.
[sql]
SELECT
RackObject.id, RackObject.name, RackObject.comment,
d1.dict_value AS OS,
REPLACE(d2.dict_value, "%GPASS%"," ") AS Hardware,
av2.string_value AS Contact
FROM
RackObject
LEFT JOIN AttributeValue AS av1 ON (av1.object_id = RackObject.id AND
av1.attr_id = 4)
LEFT JOIN Dictionary AS d1 ON d1.dict_key = av1.uint_value
LEFT JOIN AttributeValue AS av2 ON (av2.object_id = RackObject.id AND
av2.attr_id = 14)
LEFT JOIN AttributeValue AS av3 ON (av3.object_id = RackObject.id AND
av3.attr_id = 2)
LEFT JOIN Dictionary AS d2 ON d2.dict_key = av3.uint_value
WHERE
d1.dict_value LIKE "%Windows%"
ORDER BY
RackObject.id;
[/sql]
Returns for me:
+----+-----------------+---------------+--------------+---------------------+----------+
| id | name | comment | OS | Hardware
| Contact |
+----+-----------------+---------------+--------------+---------------------+----------+
| 72 | test-01 | NULL | Windows 2003 | Dell PowerEdge
2950 | NULL |
| 73 | test-02 | NULL | Windows 2003 | Dell PowerEdge
2850 | NULL |
| 74 | test-03 | NULL | Windows 2003 | Dell PowerEdge
2950 | Jonathan |
| 75 | test-04 | DB Server | Windows 2003 | Dell PowerEdge
2950 | Jonathan |
+----+-----------------+---------------+--------------+---------------------+----------+
-Jonathan
On Tue, Jun 17, 2008 at 6:54 AM, killsystem <killsystem@xxxxxxxxxxxxxxxx> wrote:
> Hello,
>
> can anyone explain to me how I have to write an SQL query that returns
> multiple attribute values?
> I want to get all servers with Windows as operating system need addtional
> information From AttributeValue.
> The query should return:
> RackObject.id, RackObject.name, RackObject.comment and from AttributeValue:
> * operating system
> * contact person
> * HW type
>
> To get all servers with Windows as os I wrote this query:
> [sql]
> SELECT RackObject.id, RackObject.name, RackObject.comment,
> REPLACE(Dictionary.dict_value,"%GPASS%"," ")
> FROM AttributeValue
> INNER JOIN RackObject ON AttributeValue.object_id = RackObject.id
> LEFT JOIN Dictionary ON Dictionary.dict_key = AttributeValue.uint_value
> WHERE AttributeValue.attr_id IN (10) OR Dictionary.dict_value LIKE
> "%Windows%" ORDER BY RackObject.id
> [/sql]
>
> But I couldn't figure out how to get the other values within this query.
>
> Thanks
>
> Fabian aka Killsystem
>
>
>
Other related posts:
