SQLTeam.com | Weblogs | Forums

Lost records


#1

Hi There,

I need your help again.
I have 2 tables WorksReq & PartsReq with a related field called PartsId
I need to list all records from WorkReq that do not have a related record in PartsReq.
For example 1 record in WorksReq has a PartsId = 167 but there is not a record in PartsReq with a PartsId = 167

How do I list all the WorkReq records with a missing PartsReq record.

Thanks for any help you can give.

Best Regards,


#2

select * from WorksReq
where PartsId not in(select PartsId from PartsReq)


#3

Hi Ahmed,
Thanks for the reply.

That worked for me, Thank you.
I now need to write the missing PartsReq records using values from the WorksReq record.
So for each WorksReq record that does not have a matching PartsReq record, insert a PartsReq Record with values PartsReq.PartsId = WorksReq.PartsId,
PartsReq.Description = WorksReq.Description,
PartsReq.DateReq = WorksReq.DateReq etc

How would I get each WorksReq field values and use them to add records into the PartsReq table.

Thanks for the help.

Best Regards,


#4

can you try this

Insert into PartsReq(PartsId,Description,DateReq)
select PartsId,Description,DateReq from WorksReq
where PartsId not in(select PartsId from PartsReq)