INSERT query with JOIN and WHERE

[cdck]

I am working with 4 tables. Two hold the data I need to pull (dbo_jodrtg and dbo_jomast), one is the table I need to insert that data into (Budgets), and one is a table that holds related data (Actuals), which I am referring to in the query entirely to limit what data is transferred into Budgets.

I need to pull only those items from the source tables in which the dbo_jomast.fsono field has a value that can be found somewhere in the Actuals.SalesOrder field.

The query works fine without limiting the data based on Actuals:
[tt]INSERT INTO Budgets ( SalesOrder, Job, WorkCtr, Dollars, Hours )
SELECT dbo_jomast.fsono, dbo_jodrtg.fjobno, dbo_jodrtg.fpro_id, Sum(dbo_jodrtg.fprod_val) AS SumOffprod_val, Sum(dbo_jodrtg.fprod_tim) AS SumOffprod_tim
FROM dbo_jomast INNER JOIN dbo_jodrtg ON dbo_jomast.fjobno = dbo_jodrtg.fjobno
GROUP BY dbo_jomast.fsono, dbo_jodrtg.fjobno, dbo_jodrtg.fpro_id
ORDER BY dbo_jomast.fsono, dbo_jodrtg.fjobno, dbo_jodrtg.fpro_id;
[/tt]

Below is the current code:

[tt]INSERT INTO Budgets ( SalesOrder, Job, WorkCtr, Dollars, Hours )
SELECT dbo_jomast.fsono, dbo_jodrtg.fjobno, dbo_jodrtg.fpro_id, Sum(dbo_jodrtg.fprod_val) AS SumOffprod_val, Sum(dbo_jodrtg.fprod_tim) AS SumOffprod_tim
FROM dbo_jomast WHERE dbo_jomast.fsono IN (SELECT Actuals.SalesOrder FROM Actuals)
INNER JOIN dbo_jodrtg ON dbo_jomast.fjobno = dbo_jodrtg.fjobno
GROUP BY dbo_jomast.fsono, dbo_jodrtg.fjobno, dbo_jodrtg.fpro_id
ORDER BY dbo_jomast.fsono, dbo_jodrtg.fjobno, dbo_jodrtg.fpro_id;[/tt]

With this code, I get an error that tells me there is a:
[tt]Syntax error (missing operator) in query expression 'dbo_jomast.fosono IN (SELECT Actuals.SalesOrder FROM Actuals) INNER JOIN dbo_jodrtg ON dbo_jomast.fjobno = dbo_jodrtg.fjobno'.
[/tt]

I tried putting the filter into the join, like so:

[tt]INSERT INTO Budgets ( SalesOrder, Job, WorkCtr, Dollars, Hours )
SELECT dbo_jomast.fsono, dbo_jodrtg.fjobno, dbo_jodrtg.fpro_id, Sum(dbo_jodrtg.fprod_val) AS SumOffprod_val, Sum(dbo_jodrtg.fprod_tim) AS SumOffprod_tim
FROM dbo_jomast
INNER JOIN dbo_jodrtg, Actuals ON dbo_jomast.fjobno = dbo_jodrtg.fjobno
AND dbo_jomast.fsono IN (SELECT Actuals.SalesOrder FROM Actuals)
GROUP BY dbo_jomast.fsono, dbo_jodrtg.fjobno, dbo_jodrtg.fpro_id
ORDER BY dbo_jomast.fsono, dbo_jodrtg.fjobno, dbo_jodrtg.fpro_id;[/tt]

It then tells me [tt]Syntax error in FROM clause.[/tt]

I'm not sure where or how to add this filter.

Cheryl dc Kern

 

[PHV]

What about this ?
INSERT INTO Budgets ( SalesOrder, Job, WorkCtr, Dollars, Hours )
SELECT dbo_jomast.fsono, dbo_jodrtg.fjobno, dbo_jodrtg.fpro_id, Sum(dbo_jodrtg.fprod_val) AS SumOffprod_val, Sum(dbo_jodrtg.fprod_tim) AS SumOffprod_tim
FROM dbo_jomast
INNER JOIN dbo_jodrtg ON dbo_jomast.fjobno = dbo_jodrtg.fjobno
WHERE dbo_jomast.fsono IN (SELECT Actuals.SalesOrder FROM Actuals)
GROUP BY dbo_jomast.fsono, dbo_jodrtg.fjobno, dbo_jodrtg.fpro_id
ORDER BY dbo_jomast.fsono, dbo_jodrtg.fjobno, dbo_jodrtg.fpro_id;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cmmrfrds]

INSERT INTO Budgets ( SalesOrder, Job, WorkCtr, Dollars, Hours )
SELECT dbo_jomast.fsono, dbo_jodrtg.fjobno, dbo_jodrtg.fpro_id, Sum(dbo_jodrtg.fprod_val) AS SumOffprod_val, Sum(dbo_jodrtg.fprod_tim) AS SumOffprod_tim
FROM dbo_jomast INNER JOIN dbo_jodrtg ON dbo_jomast.fjobno = dbo_jodrtg.fjobno

The where clause is NOT part of the Join - put it after the join but before the group by clause.
WHERE dbo_jomast.fsono IN (SELECT Actuals.SalesOrder FROM Actuals)
GROUP BY dbo_jomast.fsono, dbo_jodrtg.fjobno, dbo_jodrtg.fpro_id;

Get rid of the order by - no need and extra time spent.
ORDER BY dbo_jomast.fsono, dbo_jodrtg.fjobno, dbo_jodrtg.fpro_id;

 

[cdck]

This gets rid of the errors, but I get bad data.

Each Sales Order in the dbo_jomast table has only one Sales Order number(fsono) but multiple jobnumbers (fjobno). In the dbo_jodrtg table, no sales numbers are listed, only the jobnumbers (fjobno). From all of this data, I am trying to only pull information for Sales Orders that exist in the Actuals table, which lists the Sales Order under SalesOrder.

With the query written as you suggested, it is pulling all of the information in the dbo_jodrtg and showing it with the SalesOrder result from Actuals attached to every record.

So even though Sales Order 000039 in dbo_jomast has only jobnumbers in a range from 00035-0000 through 00035-0100, the results from this query also show jobs in ranges like 00027-0000 through 00027-1000 and 00157-0000 through 00157-1000, but with 000039 for the sales order on every line instead of their real sales order numbers.

Am I doing the joins in the wrong order to avoid this?

Cheryl dc Kern