Welcome to WebmasterWorld Guest from 100.25.214.89

Forum Moderators: open

Message Too Old, No Replies

SQL query help needed

Help trying to extract data from table where only some rows are required

     
4:45 am on Aug 2, 2007 (gmt 0)

New User

10+ Year Member

joined:Sept 16, 2003
posts:8
votes: 0


Ok I've got data in a table that's not that easy to extract, probably easier to show you...

ID, Record, Group, Item, ErrorMessage
1, 99, Person, FName, "FName contains an invalid value"
2, 99, UNKNOWN, FName, "Jack is not a known FName value"
3, 99, UNKNOWN, LName, "LName contains an invalid value"
4, 99, Contact, Phone, "Phone contains an invalid value"
5, 99, Contact, Email, "Post is not a known Email value"
6, 99, UNKNOWN, Phone, "ZZZZ is not a known Phone value"

Now what I'd like to get out of this data is:

ID, Record, Group, Item, ErrorMessage
1, 99, Person, FName, "FName contains an invalid value"
3, 99, UNKNOWN, LName, "LName contains an invalid value"
4, 99, Contact, Phone, "Phone contains an invalid value"
5, 99, Contact, Email, "Post is not a known Email value"

So I only get one error for each item and where the group is defined I want that rather than the line with "UNKNOWN" in the Group

All I know going into the query is the Record id (99)

Also it's on Oracle 10

6:31 am on Aug 4, 2007 (gmt 0)

Full Member

10+ Year Member

joined:Apr 21, 2004
posts:306
votes: 0


How about:

(SELECT DISTINCT Item, ID, Record, Group, ErrorMessage FROM table WHERE Group <> "UNKNOWN" AND Record=99)

UNION

(SELECT DISTINCT Item, ID, Record, Group, ErrorMessage FROM table WHERE Group = "UNKNOWN" AND Record=99 AND Item NOT IN(SELECT Item FROM table WHERE Group <> "UNKNOWN" AND Record=99))

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members