0
SELECT user_ids FROM `items`

When I run that query, if a user has multiple items listed, I get the user_id listed that many times, but what I really want is just ONE user_id for each so it basically represents all the user_ids who have >1 entry in the items table (and what I really want is the corresponding email)

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
adam
  • 2,830
  • 5
  • 31
  • 37

3 Answers3

2

If I understand correctly you want to use distinct

SELECT DISTINCT user_ids FROM items
Arthur Frankel
  • 4,695
  • 6
  • 35
  • 56
  • For more explanations see also: http://stackoverflow.com/questions/581521/whats-faster-select-distinct-or-group-by-in-mysql – feeela Jul 07 '11 at 23:50
  • amazing. thats exactly what i wanted. now i want an email address for each of those results... or is there a more efficient query like "display email from users where user_id ..." seems like the transition is tricky from the items table to the users table (where the emails are stored) – adam Jul 07 '11 at 23:51
  • select email_addr from items where user_ids in (select Distinct user_ids FROM items). There may be a better way but I can't think of one right now. – Arthur Frankel Jul 08 '11 at 00:12
1
Select Distinct user_ids FROM items
Mike Veigel
  • 3,795
  • 2
  • 20
  • 28
1

From your context of > 1 entry (ie: more than 1 entry), you'll need a group by, not just distinct... distinct will include everyone, even if they ONLY have ONE entry

select User_IDs
   from items
   group by User_IDs
   having count(*) > 1
DRapp
  • 47,638
  • 12
  • 72
  • 142