CODEWITHSUNDEEP
mysqlduplicates
user1002601
user1002601

Reputation: 373

Duplicated table, values of words varying even between duplicated words. Find words only have specific value

There is a table i work in Mysql. There are words and values. Values is Boolean like '1' or '0'. Words can repeat more than once. And their values vary. For example one "def" can be '0' while other is '1'. What i am trying to find is:

The table is like:

word value
----  -----
abc    1
abc    1
def    0
def    1
def    0
xyz    0
xyz    0
xyz    0
xxx    1
zzz    0

Upvotes: 0

Views: 88

Answers (2)

Marco
Marco

Reputation: 57593

I really don't understand what you're trying to obtain.
Anyway I try:

SELECT DISTINCT word FROM your_table
WHERE value = 0

If you want to sort words alphabetically, you can do

SELECT DISTINCT word FROM your_table
WHERE value = 0
ORDER BY word

EDITED in response to user comment:
Try this:

SELECT DISTINCT word,value FROM your_table
WHERE word NOT IN
  (SELECT word FROM your_table WHERE value = 1)

or

SELECT word, SUM(value) as tot FROM your_table
GROUP by word
HAVING SUM(value) = 0

Upvotes: 2

DaveyBoy
DaveyBoy

Reputation: 2924

Something along the lines of

select word,min(value) from table group by word;

which will return a 0 if a word has 0 and 1 as a value or 1 if it's only got a 1

If you specifically want only the '0' values then:

select word,value from table where value=0 group by word;

Upvotes: 0

Related Questions