Printable View
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.Code: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
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.
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
If the data always contains exactly one value for "40" and exactly one for "44", you could use an SQL statement like this:
Code: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
Thanks for the replies...but,
I forgot to mention one thing... this data is all in one table.
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.
works perfectly - thanks for all of the help.