[RESOLVED] header - line item query question

[ZeBula8]

typeinfo    invoiceNumber serviceOrder                                       
----------- ------------- ----------- 
40          123           W123a
44          123           W123b
40          124           W124a
44          124           W124b
40          125           W125a
44          125           W125b
40          126           W126a
44          126           W126b

what i would like to do is make a query that will return on ONE LINE the data that is contained with typeinfo of 40 and 44.

40 is the header and 44 is the line item.



So on ONE LINE returned it should look something like:

40 124 W124a 44 124 W124b


Is this possible? Thanks in advance.

[mikee_phil]

on your database create to separate query for 40 124 W124a and 44 124 W124b
query1:
select typeinfo ,invoiceNumber, serviceOrder from table1 where (typeinfo =40 and invoiceNumber=124 and serviceOrder ='W124a')
query2:
select typeinfo ,invoiceNumber, serviceOrder from table1 where (typeinfo =44 and invoiceNumber=124 and serviceOrder ='W124b')

then you can join the two
query3:
select query1.typeinfo,query1.invoiceNumber,query1.serviceOrder,
query2.typeinfo,query2.invoiceNumber,query2.serviceOrder
from query1,query2
this only return 40 124 W124a 44 124 W124b
but if query 1 or query 2 return more than 1 record the result will be different, like a cross join

[si_the_geek]

If the data always contains exactly one value for "40" and exactly one for "44", you could use an SQL statement like this:

SELECT t1.typeinfo, t1.invoiceNumber, t1.serviceOrder,
       t2.typeinfo, t2.invoiceNumber, t2.serviceOrder
FROM tablename t1
INNER JOIN tablename t2 ON (t1.invoiceNumber = t2.invoiceNumber)
WHERE t1.typeinfo = 40
AND t2.typeinfo = 44

[ZeBula8]

Thanks for the replies...but,
I forgot to mention one thing... this data is all in one table.

[si_the_geek]

I had assumed that, and my query will work for that (subject to my condition above). Just change both instances of tablename to the table name.

mikee_phil appears to have made the same assumption.

[ZeBula8]

works perfectly - thanks for all of the help.