HELP: Return these records, and *ONE* matching record for each...

HELP: Return these records, and *ONE* matching record for each...

Post by » Mon, 20 Aug 2007 21:51:51


I have a MS-SQL 2000 database with records like:

DOG-
CAT-
BIRD-
FISH-
DOG-47
DOG-48
CAT-AAA
CAT-123
HORSE-BBB

I need a SELECT statement that will give me a list of all the records
that end in "-"... and 1 (doesn't matter which) of its matching
counterparts.

So the results would produce:
DOG-
DOG-47 (DOG-48 is NOT needed here)
CAT-
CAT-AAA (CAT-123 is NOT needed here)
BIRD- (No matching record, so just return the "- ending"
record)
FISH- (No matching record, so just return the "- ending"
record)
(HORSE-BBB is not included at all, because there's
no "- ending" record)

Thanks.

============================================


CREATE TABLE TmpTable
(
Name VarChar(30)
)

INSERT INTO TmpTable(Name) VALUES('DOG-')
INSERT INTO TmpTable(Name) VALUES('CAT-')
INSERT INTO TmpTable(Name) VALUES('BIRD-')
INSERT INTO TmpTable(Name) VALUES('FISH-')
INSERT INTO TmpTable(Name) VALUES('DOG-47')
INSERT INTO TmpTable(Name) VALUES('DOG-48')
INSERT INTO TmpTable(Name) VALUES('CAT-AAA')
INSERT INTO TmpTable(Name) VALUES('CAT-123')
INSERT INTO TmpTable(Name) VALUES('HORSE-BBB')

SELECT * FROM TmpTable
DROP TABLE TmpTable
 
 
 

1. Record in one record matches record in another table

2. Update record - returns to the first record in the record set

Hi,
I have a form for participants in a class.
on the participant form there is a current date for the class - and a drop
down of the dates offered for the class.
I have a buttne when clicked will execute the following update query

Update OrderDetail set classDatesID = 34 Where orderDetailID=10035


problem is orderDetailID is ALWAYS the orderDetailID for the first
participant no matter which participant record I am on when executing the
query.

any idea what might be wrong?


thanks,
Kim

3. MsQuery returning one record + one empty record

4. Selection query with MAX(textfield) return null value if the matching record more than 2501 records

5. When Filtering Query - Query returns no records when there arer matching records

6. matching and merging records into one record

7. Find records in one table that match records in another

8. Matching records from data set one with records from data set

9. Deleting records in one table having a matching related record in another table?

10. field returns a value if at least one matching record is found

11. Query returns 9 records with criteria that exists in one record

12. Cursor returns to first record in subform after updating second record

13. Return only first record in set of records???

14. Need help writing a MySQL query that will return only records with matching counter-parts

15. Need help writing a MySQL query that will return only records with matching counter-parts