merge below 2 select statements

Question

merge below 2 select statements

chindilog

Old Hand

Points: 323
More actions
August 25, 2015 at 5:56 pm

#299946

SELECT
part.num, woitem.qtytarget AS woitemqty,
(SELECT LIST(wo.num, ',')
FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id
WHERE moitem.moid = mo.id) AS wonums, mo."USERID" AS mo_USERID
FROM mo
INNER JOIN moitem ON mo.id = moitem.moid
LEFT JOIN wo ON moitem.id = wo.moitemid
LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10
LEFT JOIN (Select sum(woitem.qtytarget) as labor, woitem.woid, uom.code as uom
from woitem
JOIN part on woitem.partid = part.id and part.typeid = 21
JOIN uom on woitem.uomid = uom.id
group by 2,3) as labor on wo.id = labor.woid
LEFT JOIN part ON woitem.partid = part.id
where woitem.qtytarget/wo.qtytarget IS NOT NULL
SELECT
MO."USERID" AS MO_USERID,
SYSUSER."USERNAME" AS SYSUSER_USERNAME
FROM
"SYSUSER" SYSUSER INNER JOIN "MO" MO ON SYSUSER."ID" = MO."USERID"

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

chindilog (8/25/2015)
SELECT
part.num, woitem.qtytarget AS woitemqty,
(SELECT LIST(wo.num, ',')
FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id
WHERE moitem.moid = mo.id) AS wonums, mo."USERID" AS mo_USERID
FROM mo
INNER JOIN moitem ON mo.id = moitem.moid
LEFT JOIN wo ON moitem.id = wo.moitemid
LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10
LEFT JOIN (Select sum(woitem.qtytarget) as labor, woitem.woid, uom.code as uom
from woitem
JOIN part on woitem.partid = part.id and part.typeid = 21
JOIN uom on woitem.uomid = uom.id
group by 2,3) as labor on wo.id = labor.woid
LEFT JOIN part ON woitem.partid = part.id
where woitem.qtytarget/wo.qtytarget IS NOT NULL
SELECT
MO."USERID" AS MO_USERID,
SYSUSER."USERNAME" AS SYSUSER_USERNAME
FROM
"SYSUSER" SYSUSER INNER JOIN "MO" MO ON SYSUSER."ID" = MO."USERID"

Is there a question here?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

chindilog (8/25/2015)
(SELECT LIST(wo.num, ',')

can you please explain what "SELECT LIST" does....are you using MS SQL?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

Going off the post about this query at http://www.sqlservercentral.com/Forums/Topic1714385-391-1.aspx#bm1714502, this is something called Firebird SQL.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Ed Wagner (8/26/2015)
Going off the post about this query at http://www.sqlservercentral.com/Forums/Topic1714385-391-1.aspx#bm1714502, this is something called Firebird SQL.

Interesting. As much I would be willing to help I think I will walk away from this. I know nothing about Firebird and anything I might suggest is highly likely to be incorrect for a database engine I have never heard of before.