INSERT INTO SELECT

[MrXHellboy]

Hello,

 

I have a query "INSERT INTO SELECT".

It's quite huge, so i minimize it with a simple example

 

INSERT INTO table (NAME, PRICE, CATEGORY, ORDER)

SELECT a.name, a.price, a.category, a.order

FROM table1 AS a

INNER JOIN table2 AS b

ON a.order = b.order

WHERE a.ordered <> (SELECT SUM(QTY) FROM ordered WHERE barcode = a.barcode GROUP BY barcode  )

 

@ table 1 i have 1 record showing the total ordered quantity (324 - barcode 555666), which i want to compare to the SUM of all the QTY from table2, which has 4 records (4* QTY 81 - barcode 555666).

If you get your calculator, you will see it makes 324, so the total quantity is correct.

 

But the query places 3 records into the new table and leaves 1 behind.

It must place all 4 the records into the new table instead of 3.

 

Please help ?

The thing is... I get 3 records placed into `table`

[MrXHellboy]

Couldn't change the sotry anymore...

 

Hello,

 

I have a query "INSERT INTO SELECT".

It's quite huge, so i minimize it with a simple example

 

INSERT INTO table (NAME, PRICE, CATEGORY, ORDER)

SELECT a.name, a.price, a.category, a.order

FROM table1 AS a

INNER JOIN table2 AS b

ON a.order = b.order

WHERE a.received<> (SELECT SUM(QTY) FROM shipped WHERE barcode = a.barcode GROUP BY barcode  )

 

@ table 1 i have 1 record showing the total ordered quantity (324 - barcode 555666), and the received QTY 0, which i want to compare to the SUM of all the QTY from table2, which has 4 records (4* QTY 81 - barcode 555666).

If you get your calculator, you will see it makes 324, so the total quantity is correct. But since we didn't receive anything, the received QTY is different than the shipped QTY. Now i want all those 4 records moved into the other table.

 

But the query places 3 records into the new table and leaves 1 behind.

It must place all 4 the records into the new table instead of 3.

 

Please help ?

[Muddy_Funster]

I think it may be a flaw in your logic, that when the final record is selected the value then = the (select) value rather than being <> the (select) value.  Best i can come up with from the information given.