Re: (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 :-)

Comment on Re: (OT) Exclusion selection on DBI Download Code

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]
Re^3: (OT) Exclusion selection on DBI by mpeppler (Vicar) on Jan 25, 2008 at 13:16 UTC
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]
Re^3: (OT) Exclusion selection on DBI by moritz (Cardinal) on Jan 25, 2008 at 14:14 UTC
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]


Don't ask to ask, just ask
	PerlMonks