RE: Inline View

If I understand you correctly you want the the fields to be in the rows
and the records to be in the columns not the other way around as it
usually is. I don't think you can get MySQL to return data like that
directly. You can of course arrange the data returned yourself in any
way you like by populating a grid or spreadsheet that way.

I also tried to figure out what your query is trying to do. If I
understand it correctly, I would write it like this and avoid the
subqueries.

SELECT DISTINCT i1.reportid, i2.ivalue as IPAddress, i3.ivalue as
Computer
FROM item AS i1, item AS i2, item AS i3
WHERE i2.ifield LIKE '%IP Address%'
AND i1.ivalue = i2.ivalue 
AND i1.ifield = i2.ifield
AND i3.ifield = 'Computer Name'
AND i1.ivalue = i3.ivalue
AND i1.ifield = i3.ifield
AND i1.reportid = 1
ORDER BY 1

This looks a bit odd. You don't really need i1.reportid in the selected
columns because the WHERE clause means that it is always 1 anyway. The
ORDER BY clause does nothing because it is only sorting on a column
which always has the same value. That LIKE condition will be inefficient
if the item table is big because it cannot take advantage of any index
there may be on the ifield column.

It might make more sense if I understood the nature of the data in the
item table. When you do reflexive joins it is often an indication that
you have one table holding more than one sort of entity and it might be
better to break it into more than one table. You might also find then
that getting the data out in the way you want becomes straightforward.

John Bonnett

-----Original Message-----
From: Melissa Dougherty [mailto:[email protected]] 
Sent: Thursday, 19 October 2006 11:47 AM
To: [email protected]
Subject: Inline View

I'm trying to take table data and display the data horizontal....  I
need to take the column and show the results in one row.  I have tried
several different inline view (queries) and get multiple rows.  Here is
an example.... It brings back three rows with each column in a different
row.


select distinct i.reportid,
(select ivalue from item where ifield like '%IP Address%' and ifield =
i.ifield and ivalue = i.ivalue) AS IPAddress, (select ivalue from item
where ifield = 'Computer Name' and ifield = i.ifield and ivalue =
i.ivalue) AS Computer from item i where i.reportid = 1 order by 1


Any suggestions?

Thanks,
Melissa 

-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]