Return multiple-rowed subqueries as additional columns

I have a database of support tickets I am querying. there is a main table and a status table which can have 0 or more rows per associated ticket.

Is there a way for me to return multiple rows of data from the status table into one resulting row while also querying the main ticket?

Here is what I have so far:

SELECT Ticket, Misc1, Misc2,
(SELECT TOWKGRPNAME FROM ACTREFEROUT RO WHERE Ticket='xxyyzz') rog1, FROM Ticket WHERE TKTNUM='xxyyzz'

What I want is to somehow return all rows of the subquery as diff columns in the main query, like so:

Ticket, Misc 1, Misc 2, rog1, rog2, rog3

is this even possible?

Thanks
Graeme Received on Thu Jan 13 2005 - 13:54:30 CST