Nested SQL Query

[manofsteel00]

I have a select statement to pull PICKSL, SLOT, PROD, DESC, PACK, MANU ID, HITIX, and PALLET QTY from the (PICKSL) table. The present nested select statement grabs all the records whose product numbers are NOT already in the ALTER table.

PROBLEM: I would like to pull the SLOT field from the TEST table since the PICKSL table does not have a SLOT field. Is there a way to do this in SQL? Thank you very much in advance.

sql = "INSERT INTO MAIN ( PICKSL, SLOT, PROD, [DESC], PACK, " & _
          "[MANU ID], HITIX, [PALLET QTY] ) " & _
          "SELECT p.PICKSL, p.PICKSL, p.PROD, p.DESC, p.PACK, " & _
          "p.MANUID, p.HITI, p.OH " & _
          "FROM PICKSL AS p " & _
          "WHERE Not Exists ( " & _
            "SELECT * " & _
            "FROM [ALTER] a " & _
            "WHERE  a.PROD = p.PROD);"

[Al42]

PROBLEM: I would like to pull the SLOT field from the TEST table since the PICKSL table does not have a SLOT field. Is there a way to do this in SQL?

If there's a link field between the Test table and the PickSL table you could use a Join.

[mendhak]

Nested queries are expensive and if you have some sort of a primary key that both tables 'know', you should use a JOIN. Also, why are you PICKing PICKSL twice? ( )

SELECT p.Picksl, p.Prod, p.Desc, p.Pack, p.Manuid, p.hiti, p.oh, t.Slot FROM
PICKSL P LEFT JOIN Alter A ON A.Prod = B.Prod 
LEFT JOIN Test T ON P.SomeID = T.SomeID
WHERE B.Prod IS NULL

[manofsteel00]

The PICKSL table does not have the SLOT field; it has everything but the slot field. I would like to create a query that pulls the SLOT field from the TEST table and the rest of the information from the PICKSL table to populate a listbox. My current query pulls all the information correctly except for the SLOT field, which is why I am currently just using the pick slot for the slot field.

[manofsteel00]

The following code actually gives me the appropriate information EXCEPT if a product is not listed in the TEST table it will not be inserted to the MAIN table. In other words, there are some items that do not appear on the TEST table; therefore, their SLOT should be the same as their PICKSL. Would it just be better to loop through the database and set the SLOTs appropriately or is there a way I can retrieve all the records from the PICKSL table even though their SLOT is not on the TEST table? Please feel free to post any questions for clarity. Thank you in advance.

INSERT INTO MAIN ( PICKSL, SLOT, PROD, [DESC], PACK, [MANU ID], HITIX, [PALLET QTY] )
SELECT p.PICKSL, t.SLOT, p.PROD, p.DESC, p.PACK, p.MANUID, p.HITI, p.OH
FROM PICKSL AS p, TEST AS t
WHERE Not Exists ( 
            SELECT * 
            FROM [ALTER] a
            WHERE  a.PROD = p.PROD)
     AND t.PROD = p.PROD;