Compare rows in the same table to look for matching records

Question

Compare rows in the same table to look for matching records

AIRWALKER-375999

Hall of Fame

Points: 3040
More actions
November 12, 2008 at 11:05 am

#383363

Hi, I'm trying to find a way to campare values in rows of the same table without using joins. for example I have a table like this:
QUESTION_ID | POSSIBLE_ANSWERS | PARENT |
A 1 NULL
A 2 NULL
A 3 NULL
A 4 4
In the above table the final row has matching values (4), so my question how can I CASE select the the row with the match values? Kinda like this:
SELECT QUESTION_ID, POSSIBLE_ANSWERS, PARENT,
CASE PARENT WHEN PARENT = POSSILE_ANSWERS THEN 'Y'
ELSE 'N'
AND AS 'CORRECT?'
So I would want to get back something like this:
QUESTION_ID | POSSIBLE_ANSWERS | PARENT |CORRECT|
A 1 NULL N
A 2 NULL N
A 3 NULL N
A 4 4 Y
Is this possible without using joins, any help will be greatly appreciated.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Sure you can do exactly what you propose except you can't wrap CORRECT in single quotes. Since the data you are comparing is in the same row you do not need to do any joining.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

AIRWALKER-375999 Hall of Fame Points: 3040 More actions · Answer 2

Thanks for your reply. Is it possible to do the same kind of camparing when the data is in the same tablle but in different rows?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Sure it is possible, but then you need to use joins.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sgmunson SSC Guru Points: 110640 More actions · Answer 4

Just to provide a syntactically correct set of T-SQL:

SELECT QUESTION_ID, POSSIBLE_ANSWERS, PARENT,

CASE

WHEN PARENT = POSSIBLE_ANSWERS THEN 'Y'

ELSE 'N'

END AS CORRECT

FROM THE_TABLE_NAME

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

epriddy Old Hand Points: 343 More actions · Answer 5

If you want to know if it's in any row within the table then try:

SELECT QUESTION_ID,

POSSIBLE_ANSWERS,

PARENT,

CORRECT = CASE WHEN PARENT in (Select Possible_Answers from THE_TABLE_NAME) THEN 'Y'

ELSE 'N'

END

FROM THE_TABLE_NAME

Nice & straight-forward :>)