Difficult Select Statement in SQL

560 pts.
Tags:
SQL
SQL Select
SQL Select statement problem
I have a table with columns, to wit:
FieldA FieldB
A U
B V
C A
C A
C B
D A
D A
D A
E B
E B
E B
I can get the records that are multiple in FieldA but whose values in FieldB are repeating with:
Select FieldA, FieldB from table 
group by FieldA, FieldB 
having count(*) > 1
And the result will be:
FieldA FieldB
D A
E B
But how do I get the records that are multiple in FieldA but whose values in FieldB are not repeating?


Software/Hardware used:
SQL

Answer Wiki

Thanks. We'll let you know when a new response is added.

It sounds like you want all of the duplicates in A regardless of the duplicates in B
<pre>
select * from table1
where flda in(
select flda from table1
group by flda
having count(*) > 1)
</pre>

————-

If I understand correctly, I think you would have to add an additional condition to the query provided by BigKat.

Something like this:

<pre>
select * from table1 <b>t</b>
where flda in(
select flda from table1
group by flda
having count(*) > 1)
<b>AND EXISTS
(SELECT 1
FROM table1
WHERE flda = t.flda
AND fldb != t.fldb)</b></pre>

This would be inefficient, but without more details I don’t think we can offer something radically different.

-CarlosDL

You can cut out the first subquery to improve performance, as by default, the 2nd subquery will only pick up records that have count(*) > 1. Also, if you want to remove repeat values you can use the distinct clause e.g. these would all be returned
A,B
A,C,
A,B

So:
select distinct flda, fldb from table1 t
where exists
(select 1 from table1 where flda = t.flda and fldb !=t.fldb)
order by 1,2 –(to keep all the customers purchases together)

-Darryn
————-

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Gent01
    Can you post what result you would like to see?
    1,870 pointsBadges:
    report
  • bogeybetsy
    I want to see: FieldA FieldB
    560 pointsBadges:
    report
  • bogeybetsy
    Sorry about that. This text editor here really sucks. Anyway, I'd like to see this result: FieldA FieldB C A C A C B
    560 pointsBadges:
    report
  • bogeybetsy
    That is, I want to know which records are multiple in FieldA but not constant in FieldB...
    560 pointsBadges:
    report
  • bogeybetsy
    Look at it this way. FldA represents customers. Fldb represents the products the customers buy. I want to know which customers buy different products and what products. Big Kat's answer was incorrect. But your answer did the trick!!! It worked! I just don't understand it. I have to analyze the data first. But I'll get back to learning and understanding how your code worked. Thanks to everyone for the big help!
    560 pointsBadges:
    report
  • bogeybetsy
    By the way, how do I append that "t." to a concatenated string? I have to concatenate fielda and fieldc. I did it this way: t.(digits(fielda)||fieldc) Of course, it didn't work....how do I do this?
    560 pointsBadges:
    report
  • bogeybetsy
    How do I append that 't.' to a concatenated string? t.digits(fieldx)||fieldb doesn't work....
    560 pointsBadges:
    report
  • carlosdl
    I'm glad it worked. You should prefix each field with the table alias. Something like this (assuming that '||' is the concatenation operator for your platform): (digits(t.fielda)||t.fieldc)
    67,965 pointsBadges:
    report
  • bogeybetsy
    Hey Carlos, Thank you very much. You were a big help. I also remember you answered an SQL questionof mine before. Thanks very much!
    560 pointsBadges:
    report
  • carlosdl
    "You can cut out the first subquery to improve performance, as by default, the 2nd subquery will only pick up records that have count(*) > 1" Very good observation Darryn.
    67,965 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following