Unknown column username in 'where clause'

Question

I got this error when I try to select on table that one of its column is foreign key:

select count(*) from cards where username = 'name';

I am doing it through java jdbc so I think something with my mapping is wrong because the same query on through MySql commandline works just fine:

<many-to-one name="users" class="table" update="false" insert="false" fetch="select">
        <column name="username" length="45" not-null="true" />
</many-to-one>

The

2013-03-02 12:19:03,660  INFO [http-bio-8080-exec-5] (NullableType.java:203) - could not read column value from result set: username; Column 'username' not found.
2013-03-02 12:19:03,663  WARN [http-bio-8080-exec-5] (JDBCExceptionReporter.java:100) - SQL Error: 0, SQLState: S0022
2013-03-02 12:19:03,664 ERROR [http-bio-8080-exec-5] (JDBCExceptionReporter.java:101) - Column 'username' not found.

I fount this:

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html#d0e13696

sections 16.1.2 and 16.1.3 probably the answer for my issue but I cant figure it out...

(1) Check that you're selecting from the right table. (2) Check that you're connecting to the right database. — NPE, Mar 02 '13 at 10:41

score 2 · Answer 1 · answered Mar 02 '13 at 10:49

2

The error message states:

could not read column value from result set: username; Column 'username' not found.

The result set for select count(*) from ... does not have a column named username. It only consists of one column, the count.

answered Mar 02 '13 at 10:49

NPE

486,780
108
951
1,012

@user1692261: I am not sure I follow. You supply `'name'` to the query, and get back the count *for that name*. – NPE Mar 02 '13 at 10:56
This is a many to one table. for every usarname attributs there might be several rows in this table. For example, I want to count how many rows I have in the table for the username NPE. – user1692261 Mar 02 '13 at 11:03

score 0 · Answer 2 · answered Mar 02 '13 at 10:52

0

try to execute

 select count(*) from cards where `cards`.username='name';

answered Mar 02 '13 at 10:52

Ilya Boltnev

1,031
3
13
27

score 0 · Answer 3 · answered Mar 02 '13 at 19:41

0

for get fields Should not use of "Select *".
should use of "select count(username) from cards where username = 'name';" expression.

answered Mar 02 '13 at 19:41

user1874800

339
1
4
11

Unknown column username in 'where clause'

3 Answers3