SQLTeam.com | Weblogs | Forums

Need to return rows based on other table


#1

Hi There,
I have been asked by a friend to help find some rogue records.
There are 2 tables and I need to find records in Table1 that should not be there.
In Table1 the description field of the records I am interested in always begins with "ARC - " and I want to find all the records in Table1 that have the description that begins with "ARC - " and Table1.Autoid is not in the Table2 field ARCId

Does that make sense?

I have tried but I keep getting records returned that are correct and should be there.
In essence what I need is to show all records from Table1 that have a description beginning with "ARC - " and it's AutoId can not be found in Table2

Thanks for your help.

Best Regards,

Steve.


#2

What does your query look like now? Does this get you started?

SELECT mycolumn 
FROM Table1 
LEFT JOIN Table2 ON Table1.Autoid = Table2.Autoid 
WHERE Table1.mycolumn LIKE 'ARC - '
   AND Table2.Autoid IS NULL;

#3

Hi There,

Thanks for the reply.
The query only returns 1 record. There should be more than that.

Appreciate the help.

Best Regards,

Steve.


#4

Can you show your query that is only returning a single record? The solution above should get you pretty close to what you're looking for. The like clause probably should have a wild card, "LIKE 'ARC -%' "


#5

@SamuelClay, good catch on the missing wildcard. I actually did that today while looking at some data and needed to correct. :confused: