Query Help, I think I need a JOIN...

Good Morning

I have searched the BB but did not find any information to help...

I have 1 table that has serial number and model number. The serial numbers are "good" but the model numbers are not always typed in correctly. So, I have to go out to a cross-reference table that has serial number to model number relationship. What I need to do is find matching serial numbers in
the table where the model number in the cross reference table starts with a '37'.

Here is my attempt:

select
a.dlr_preg_model_no,
a.dlr_preg_serial_no,
b.dlr_inv_model_no,
b.dlr_inv_serial_no
from
ecd.stage_dlr_product_reg@ECM_TEST a,
dlr_inv_model_no b
where
b.dlr_inv_serial_no = a.dlr_preg_serial_no
and left(rtrim(b.dlr_inv_model_no), 2) in ('37')

The problem is no records are returned. However, I can verify manually that there are serial numbers with model numbers in the cross-reference table that start with '37' and are in the other table.

Any ideas? Do I need a Join in the somewhere?

Thanks for the help,
EJD

If the part typed bad (I suposse that in a textbox) you must use a combobox so the user just need to pick the correct part, and you can forget the main problem ;)

What database are you working with? (Access, SQL Server, Oracle). To check for duplicates or return only records that reside in one table you can use an IN, EXISTS LEFT JOIN query. Something like:




SELECT T.field1,T.field2 FROM T1 T
WHERE T.ID NOT IN (SELECT A_ID FROM T2 M WHERE T.ID = M.A_ID)
AND Left(T.TableField, 2) = '37'