[racktables-users] Re: SQL query question: Multiple attribute values with one query

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: