Looking for NOT Distinct Values

Question

Post reply

Looking for NOT Distinct Values

oleson

SSC-Addicted

Points: 424
More actions
April 8, 2008 at 3:25 pm

#185121

I have a table with all the field names and tables in my database:
FIELD_NAME, TABLE_NAME
Some of the tables are LUTs, and they have the same field name present in them as the table they tie to.
I kind of want to do the OPPOSITE of "Distinct", in as much as I want all of the duplicate field names so I can see only the Table with it's companion LUT. Is there a way to do "Not Distinct" as a selection criteria?
As an example, in the attached graphic, the records highlighted in yellow are the ones I want, the ones in red ALMOST fit the criteria but I don't want them, nor do I want the non-highlighted records.
Thanks in advance...
Brad

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

This won't handle the RED records, but it will get you close. 😀

SELECT FIELD_NAME, TABLE_NAME

FROM yourTable

WHERE FIELD_NAME IN (SELECT FIELD_NAME

FROM yourTable

GROUP BY FIELD_NAME

HAVING COUNT(*) > 1)

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

oleson SSC-Addicted Points: 424 More actions · Answer 2

Thanks, Joel - that does get me a little closer. I guess I need to figure out how to filter out the tables that don't connect directly to the LUT.

Brad

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

Heh... nice job there "Joel" 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

If you know that TABLE_NAME is fixed across any given FIELD_NAME, you can simplfy Joel's query like so:

SELECT FIELD_NAME, MIN(TABLE_NAME)

FROM yourTable

GROUP BY FIELD_NAME

HAVING COUNT(*) > 1)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 5

Jeff Moden (4/9/2008)
Heh... nice job there "Joel" 😛

Hey, who the heck is this Joel guy? :hehe:

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

oleson SSC-Addicted Points: 424 More actions · Answer 6

Joel...Jason...to-MAY-toe...to-MAH-toe...whatever works! 😉

Thanks for the replies guys...and Joel, whereever you are...!

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 7

LOL, I've been called worse.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Jason Selburg (4/10/2008)
Jeff Moden (4/9/2008)
Heh... nice job there "Joel" 😛
Hey, who the heck is this Joel guy? :hehe:

I hope that you're not trying to take credit for Joel's fine work in this forum, sir. He won't be happy to hear that.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]