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
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

      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

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!
      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.