Left Join problem?

[tbarone]

Basicly this has to do with payment types. I have a table which contains all my payment types. Then I have another table which has an amount for each payment type per drawer number.

This is what the twowould look like if correct

Table PaymentTypes
------------
00000001
00000002
00000003
00000004

Table Drawer Detail
-------------------
column Drawer number column Payment Type
-------------------- -------------------
00000001 00000001
00000001 00000002
00000001 00000003
00000001 00000004
00000002 00000001
00000002 00000002
00000002 00000003
00000002 00000004

This is what my select statement looks like now
Select * from PAYMENTTYPES LEFT JOIN DRAWERDETAILS ON DRAWERDETAILS.PAYMENTNUMBER =
PAYMENTTYPES.PAYMENTNUMBER AND DRAWERDETAILS.DRAWERFIGUREID = '0000000261' AND DRAWERDETAILS.PAYMENTNUMBER IS NOT NULL

And it gives me all records from PaymentTypes table.

What I actually need is an insert query that will insert all the payment types from the types table into the drawer detail table where that type is not already in the drawer detail table for that certain drawer #?

Thanks,
Tony

 

[JohnDTampaBay]

How does this work:

INSERT DrawerDetails (DrawerID, PaymentTypeID)
SELECT DISTINCT dd.DrawerID, pt.PaymentTypeID
FROM DrawerDetails dd, PaymentTypes pt
WHERE pt.PaymentTypeID NOT IN
          (SELECT paymentTypeID FROM DrawerDetails dd2
           WHERE dd2.DrawerID = dd.DrawerID)
ORDER BY dd.DrawerID, pt.PaymentTypeID

--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
(0 row(s) affected)

 

[tbarone]

Made some little tweaks and worked great!

Thanks a lot

Tony