2 values from 2 sub-querys to one row

I have two sub-querys that I need to join together;
-both sub-querys on the same row (provided the matching fields match)
-if they do not match, take the rows separately

Furthermore, I have got two values, A and B, table 1 holds values of A, where table 2 holds the values of B.

I need to get both of the values onto the same row if the querys rows match.

First query returns a table looking like:
PROJID, WORK_NUMBER, LAJI, ORDERID, ORDER_ROWNUM, ITEMID, ITEMNAME, COMPANYNAME, SIDOTUT

The second returns:
PROJID, WORK_NUMBER, LAJI, ORDERID, ORDER_ROWNUM, COMPANYNAME, TOTEUTUNEET

Matching fields are:
1st criteria)
PROJID=PROJID, WORK_NUMBER=WORK_NUMBER LAJI=LAJI
2nd criteria)
AND ORDERID=ORDERID ORDER_ROWNUM=ORDER_ROWNUM
OR NOT ORDERID=ORDERID ORDER_ROWNUM=ORDER_ROWNUM

The first AND tells me to fetch ITEMID and ITEMNAME, the other (OR NOT) says that there is no matching rownums, so we can't take itemname, but we can merge on the first criteria.

The final result should then be in this form:
PROJID, WORK_NUMBER, LAJI, ORDERID, ORDER_ROWNUM, ITEMID, ITEMNAME, COMPANYNAME, SIDOTUT, TOTEUTUNEET

Where itemname and itemid are null if the order_rownums+orderids did not match in both the tables (note: orderid always exists in the first query, while it can be null in the second)

Does anyone have any idea how to merge two tables into one table (using the sub-querys, 1st) EXCEL_SUM_PUR 2nd) EXCEL_SUM_REP), placing the different values of SIDOTUT and TOTEUTUNEET from each table onto the same row?

I find this very tricky, I tried using a join query, as I did in a previous query where I was merging two tables of SIDOTUT, but now I have two different values, and they have to go into different columns in the final table.

Finally, here's the query I've been trieing out, which returns one table, using the two sub-querys, however, it places first the first querys data, then the seconds data, without placing them onto same row as it should have.

SELECT P.PROJID
, P.WORK_NUMBER
, P.LAJI
, P.ORDERID
, P.ORDER_ROWNUM
, P.ITEMNAME
, P.COMPANYNAME
, P.SIDOTUT
, iif(isnull(R.TOTEUTUNEET),0,R.TOTEUTUNEET) AS TOTEUTUNEET
FROM EXCEL_SUM_PUR P
left outer
join EXCEL_SUM_REP R
on P.PROJID = R.PROJID
AND P.WORK_NUMBER = R.WORK_NUMBER
AND P.LAJI = R.LAJI
AND P.ORDERID = R.ORDERID
AND P.ORDER_ROWNUM = R.ORDER_ROWNUM
UNION ALL SELECT R.PROJID
, R.WORK_NUMBER
, R.LAJI
, R.ORDERID
, R.ORDER_ROWNUM
, NULL AS ITEMNAME
, NULL AS COMPANYNAME
, NULL AS SIDOTUT
, R.TOTEUTUNEET AS TOTEUTUNEET
FROM EXCEL_SUM_PUR P
right outer
join EXCEL_SUM_REP R
on P.PROJID = R.PROJID
AND P.WORK_NUMBER = R.WORK_NUMBER
AND P.LAJI = R.LAJI
AND P.ORDERID = R.ORDERID
AND P.ORDER_ROWNUM = R.ORDER_ROWNUM
where P.PROJID is null;


Hena

awh, doesn't anyone have any ideas that might help? throw them at me
I'm kinda stuck atm otherwise :/

I need to clarify this some more I think, the query I've been using now gives the results on the same rows as it should, however, it ALSO places the second querys data after the first "JOIN ROWS" query.

Let me demonstrate

EXCEL_SUM_REP returns:

PROJID WORK_NUMBER LAJI ORDERID ORDER_ROWNUM COMPANYNAME TOTEUTUNEET
NA NA051 WD PN12757 5 TEACO 1728,96
NA NA051 WD PN12757 2 TEACO 345,79
NAAA T34000 WA PN08559 RINTE_EUR 12330,45


EXCEL_SUM_PUR returns:

PROJID WORK_NUMBER LAJI ORDERID ORDER_ROWNUM ITEMID ITEMNAME COMPANYNAME SIDOTUT
NA NA051 WD PN12757 5 URW-WDS-YKS YKSH,SUUNNITTELU TEACON_EUR 1728,96
NA NA051 WD PN12757 2 URW-WDS-YKS YKSH,SUUNNITTELU TEACON_EUR 345,79
NA NA055 WD PN13476 7 URW-WDS-YKS YKSH,SUUNNITTELU TURUN SÄHKÖSUUN 50,27


EXCEL_SUM_REP&PUR returns: (at the moment)

PROJID WORK_NUMBER LAJI ORDERID ORDER_ROWNUM ITEMNAME COMPANYNAME SIDOTUT TOTEUTUNEET
NA NA051 WD PN12757 5 YKSH,SUUNNITTELU TEACON_EUR 1728,96 1728,96
NA NA051 WD PN12757 2 YKSH,SUUNNITTELU TEACON_EUR 345,79 345,79
NA NA055 WD PN13476 7 YKSH,SUUNNITTELU TURUN SÄHKÖSUUN 50,27 0



So, where the projid,wnumber,laji,orderid,orderrownum matches we put the values onto same row, if they don't match, put sidotut and toteutuneet=0 onto that row, AND we need a second row with sidotut=0 and toteutuneet.

My query below does that ... ALMOST.. :P
Here's what it returns in succession,

PROJID WORK_NUMBER LAJI ORDERID ORDER_ROWNUM ITEMNAME COMPANYNAME SIDOTUT TOTEUTUNEET
NA NA051 WD PN12757 5 1728,96
NA NA051 WD PN12757 2 345,79
NAAA T34000 WA PN08559 12330,45

Which means, it just put all the values from Toteutuneet query (EXCEL_SUM_REP), I need to somehow get the query to return the two querys onto one row, if they match, if they don't, return the values that didn't match individually, with 0 for sidotut, and another row with toteutuneet 0.

I hope that clarified, albeit I notice I ramble alot.



Query looks like this:
SELECT iif(isnull(P.SIDOTUT),NULL,P.PROJID) AS PROJID /* P.sidotut SHOULD never be null, this is just in case*/
, P.WORK_NUMBER
, P.LAJI
, P.ORDERID
, P.ORDER_ROWNUM
, P.ITEMNAME
, P.COMPANYNAME
, P.SIDOTUT
, iif(isnull(R.TOTEUTUNEET),0,R.TOTEUTUNEET) AS TOTEUTUNEET
FROM EXCEL_SUM_PUR P
left outer
join EXCEL_SUM_REP R
on P.PROJID = R.PROJID
AND P.WORK_NUMBER = R.WORK_NUMBER
AND P.LAJI = R.LAJI
AND P.ORDERID = R.ORDERID
AND P.ORDER_ROWNUM = R.ORDER_ROWNUM
UNION ALL SELECT R.PROJID
, R.WORK_NUMBER
, R.LAJI
, R.ORDERID
, R.ORDER_ROWNUM
, NULL AS ITEMNAME
, NULL AS COMPANYNAME
, NULL AS SIDOTUT
, R.TOTEUTUNEET AS TOTEUTUNEET
FROM EXCEL_SUM_PUR P
right outer
join EXCEL_SUM_REP R
on P.PROJID = R.PROJID
AND P.WORK_NUMBER = R.WORK_NUMBER
AND P.LAJI = R.LAJI
AND P.ORDERID = R.ORDERID
AND P.ORDER_ROWNUM = R.ORDER_ROWNUM
where P.PROJID is null;