http://qs321.pair.com?node_id=664272


in reply to (OT) Exclusion selection on DBI

Obviously not a perl question, however: 
select fruit, color
  from fruit_table
 where fruit in (select fruit 
                   from fruit_table
                  group by fruit
                  having count(*) > 1)
 order by fruit

[download]
should normally work (untested, though).

Michael

Update: moritz's solution is cleaner (and probably faster, if it matters), but has an additional column in the result set (which probably doesn't matter, so a better solution overall :-)

Replies are listed 'Best First'.
Re^2: (OT) Exclusion selection on DBI
by roboticus (Chancellor) on Jan 25, 2008 at 13:04 UTC
    mpeppler:

    Your solution does however offer the chance to bring the thread back on topic. He could write a perl script to strip off the unwanted column! ;^)

    Oh, by the way, if you should ever travel to Louisville, KY, let me know. I owe you a couple cases of beer or some such for all your work on the Sybase modules, etc. They've helped me quite a bit when I started with perl and Sybase.

    ...roboticus

[reply]
      I'll accept a virtual case :-) - the probability of my going anywhere near Louisville is rather small at the moment (I'm in Switzerland...)

      Michael

[reply]
Re^2: (OT) Exclusion selection on DBI
by fernandes (Monk) on Jan 25, 2008 at 14:08 UTC
    Thank you very much for the advices. They have given to me deep insights about my problem. But I need to apologize I’ve not described the problem appropriately. I will try it again, here:

    The complete table I have is like:

    Fruit, colour, Id number of supermarket

    I need to be able to select only the repeated fruits in two given supermarkets. So, if I have

    Mellon, red, 1
    Mellon, green, 2
    Banana, black, 1
    Orange, blue, 3

    And if I want to get information about supermarket 1 and 2, the result will be

    Mellon, red, 1
    Mellon, green, 2

    I’m using DBI module.

    Thanks!
[reply]
      The additional requirement of the supermarket id just adds a WHERE clause WHERE supermarket in (1, 2).

      And of course the supermarket ID has to appear in your SELECT list.

[reply]
[d/l]

In Section Seekers of Perl Wisdom