Sqlite3 Obtaining Data From Rows with Similar values in columns

Question

I have a table set up like:

Mobs
========
Name   Room
-----------
a cat    2
fido     2
human    1

I am trying to set an sql query statement that extracts the name if each row has the same value of room.

This is my code so far, but it returns an empty list:

class DBConnect(object):

    gamedb = 'game.db'

    def __init__(self):
        pass

############ TABLE PlayerInfo ###################
    def modify_data(self, string, dbfile, mode='get', fetch='all'):

        db = sqlite3.connect(dbfile)
        db.row_factory = lambda cursor, row: row[0]
        c = db.cursor()
        data = ''

        if mode == 'get':
            c.execute(string)
            if fetch == 'all':
                data = c.fetchall()
            elif fetch == 'one':
                data = c.fetchone()
            db.close()

            return data

        elif mode == 'update' or mode == 'insert':
            # update data
            c.execute(string)
            db.commit()
            db.close()

        else:
            print 'Something went wrong\nAborting Program...'
            sys.exit()

def mob_getname(self, value, using="id"):

    if using == "id":
        query = "SELECT name FROM Mobs WHERE ID=%s" % value
        return DBConnect().modify_data(query, DBConnect.gamedb)[0]
    elif using == "room":
        query = "SELECT name, room from Mobs " \  
                "GROUP BY name, room " \
                "HAVING COUNT(name) > 1" 
        return DBConnect().modify_data(query, DBConnect.gamedb)
    else:
        print 'Wrong value of using'
        return None

Trying:

print Mobs().mob_getname(2, using="room")

Comes up with an empty list?

I want to get a list that shows

['a cat', 'fido']

Thank you

UPDATE:::

I have tried replace the query that gets executed with:

query = "SELECT COUNT(*), room FROM Mobs GROUP BY Mobs " \
        "SELECT COUNT(*), room FROM Mobs GROUP BY room HAVING    COUNT(*)>1 " \
        "SELECT name FROM Mobs WHERE room=%s " % value

Now I get an error: sqlite3.OperationalError: near "SELECT": syntax error

Answer 1

You are not using group by and having clauses properly. Group by will try to group all the columns where that column matches. So if you group by name none of your rows will collapse and none will match the criteria of having count > 1

If I'm reading your question you simply need to

SELECT name FROM mobs WHERE room = %s

since you know the room number ahead of time.

Note, your second attempt is failing because you need ; between your queries

Answer 2

You cannot just put multiple independent SELECT queries one after the other.

You need a GROUP BY to determine rooms with more than one entry, and you then need a separate query to return all entries of those rooms:

SELECT Name, Room
FROM Mobs
WHERE Room IN (SELECT Room
               FROM Mobs
               GROUP BY Room
               HAVING COUNT(*) > 1);

Answer 3

To obtain the desired result list that you have given, your query would simply be:

select Name from Mobs where Room = 2