CODEWITHSUNDEEP
sql
Karen
Karen

Reputation: 173

SQL Union Not Working as I Want it To?

I am quite new to SQL and trying to expand into using unions, so I'm not 100% sure if it's possible to get the result I want with a union.

My code is the following: 

   select workorder.wonum, null as description, null as quantity, null as unitcost, null as matlincost2, null as matlinprice,
    labtrans.refwo, labtrans.startdate, labtrans.starttime, labtrans.finishtime, labtrans.regularhrs, labtrans.plusplineprice, labtrans.laborcode, labtrans.linecost as labline
    from workorder
    left join labtrans on labtrans.refwo=workorder.wonum and labtrans.siteid=workorder.siteid
    where workorder.wonum='10192'
    union
    select matusetrans.refwo, matusetrans.description, matusetrans.quantity, matusetrans.unitcost, matusetrans.linecost as matlincost2, matusetrans.plusplineprice as matlinprice,
    null as refwo, null as startdate, null as starttime, null as finishtime, null as regularhrs, null as plusplistprice, null as laborcode, null as labline
    from matusetrans
    where matusetrans.refwo='10192'
    ;

My result is the following: enter image description here

As you can see it puts the result onto 4 individual records, but I'm wanting the result on only 2 records if that makes sense.

Can this be done using union? I've tried a join instead of a union but it then brings in the 4 records anyway.

Upvotes: 0

Views: 80

Answers (1)

RabidWolverine
RabidWolverine

Reputation: 46

Your union produce the result. you must to see your nulls:

select workorder.wonum, **null as description**, null as quantity, null as unitcost, null as matlincost2, null as matlinprice,
labtrans.refwo, labtrans.startdate, labtrans.starttime, labtrans.finishtime, labtrans.regularhrs, labtrans.plusplineprice, labtrans.laborcode, labtrans.linecost as labline
from workorder
left join labtrans on labtrans.refwo=workorder.wonum and labtrans.siteid=workorder.siteid
where workorder.wonum='10192'

The first part of union, returns null in description (3 and 4 rows).

select matusetrans.refwo, matusetrans.description, matusetrans.quantity, matusetrans.unitcost, matusetrans.linecost as matlincost2, matusetrans.plusplineprice as matlinprice,
**null as refwo**, null as startdate, null as starttime, null as finishtime, null as regularhrs, null as plusplistprice, null as laborcode, null as labline
from matusetrans
where matusetrans.refwo='10192'

returns null in refwo in rows 1 and 2.

Union works select all the results from a select and add other select: not group data, only add.

if you have, por example, a select with 5 rows Union select with 3 rows

returns select with 8 rows.

Maybe you can use a join, not union as @jens says, or a group by after union, or a distinct by selected fields, you must to analyze what do you need.

Upvotes: 1

Related Questions