Need help combining two queires

  • Hey.

    So for this odbc spreedsheet im making here is the columns they want:

    Date, Open PO, Vendor, JO Assigned, PO w/o ship to Us , Dollar Ammount.

    PO = Purchase Order

    JO = Job Order

    The one in bold, PO w/o ship to Us , is the one I had to make a seperate query for because I could not use a subquery:

    qNoShipTo

    SELECT fpono

    FROM dbo.pomast

    WHERE (fstatus = 'Open') AND (fcshipto <> 'SELF') OR

    (fstatus = 'Released')

    Seems simple enough. When the status of the Purchase Order is 'Open' or 'Released' and fcship to <> SELF. Now, I need to combine it with this query:

    SELECT dbo.pomast.forddate AS [Open Date], dbo.pomast.fpono AS [Open PO], dbo.pomast.fcompany AS Vendor,

    dbo.jomast.fjobno AS [JO Assigned], SUM(dbo.poitem.fucost * dbo.poitem.fordqty) AS [Dollar Amount]

    FROM dbo.pomast INNER JOIN

    dbo.poitem ON dbo.pomast.fpono = dbo.poitem.fpono INNER JOIN

    dbo.jomast ON dbo.poitem.fpartno = dbo.jomast.fpartno AND dbo.poitem.frev = dbo.jomast.fpartrev LEFT OUTER JOIN

    dbo.jodbom ON dbo.jomast.fpartno = dbo.jodbom.fbompart AND dbo.jomast.fpartrev = dbo.jodbom.fbomrev

    WHERE (dbo.pomast.fstatus = 'OPEN')

    GROUP BY dbo.pomast.fpono, dbo.pomast.fcompany, dbo.jomast.fjobno, dbo.pomast.forddate

    HAVING (dbo.pomast.forddate > CONVERT(DATETIME, '2010-01-14 00:00:00', 102))

    ORDER BY dbo.pomast.forddate DESC

    I am having trouble doing so. I tried using a subquery but it say's I can with a groub by clause. I tried using 2 queries and combinging them by their 'key' (fpono) but it yields no results... any advice/tips? Thanks 🙂

  • Can u provide sample table relevant to ur problem?.. so that it wil be easy to help out

  • I appreciate the help. I talked with the lady that knows SQL here. I didnt even need to add the JOB side of the project. Just the PO side. Here is the completed code if you're interested:

    SELECT dbo.pomast.forddate AS [Open Date], dbo.pomast.fpono AS [Open PO], dbo.pomast.fcompany AS Vendor,

    dbo.pomast.fcshcompan AS [PO shipped to], dbo.poitem.fjokey AS [JO Assigned], SUM(dbo.poitem.fucost * dbo.poitem.fordqty) AS [Dollar Amount]

    FROM dbo.pomast INNER JOIN

    dbo.poitem ON dbo.pomast.fpono = dbo.poitem.fpono

    WHERE (dbo.pomast.fstatus = 'OPEN')

    GROUP BY dbo.pomast.fpono, dbo.pomast.fcompany, dbo.pomast.forddate, dbo.poitem.fjokey, dbo.pomast.fcshcompan, dbo.pomast.fcshipto

    HAVING (dbo.pomast.fcshipto <> 'SELF')

    ORDER BY dbo.pomast.forddate DESC

Viewing 3 posts - 1 through 3 (of 3 total)

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