Problem with construction of a query
I find it hard to build a query. Knowing the user's nickname, I want to display the name of their group assignment (attribute name of the table groups). The id attribute of the table groups is related with attribute id_object table users_groups.
I thought about this query:
select name from groups where id = (select id_group from users_groups where id_object = (select id_user from users where nickname = "foo");
But is there not something cleaner and simpler than subqueries?
Re: Problem with construction of a query
It is simpler:
SELECT name FROM groups, users, user_groups WHERE id = id_groups AND id_object = id_user AND nickname LIKE "foo";
If the three tables are in the same basis, otherwise you must use the "inner join"
Re: Problem with construction of a query
To explain the problem, I modified the post in the unique identifier of the table users. Thus, id became id_user. Now I work on a relational database schema that I can not change. The query advised to me logically generates this error message:
Quote:
#1052 - Column 'id' in where clause is ambiguous
Re: Problem with construction of a query
Add the table name like this:
SELECT name FROM groups, users, user_groups WHERE groups.id = users_groups.id_groups AND users_groups.id_object = users.id_user AND nickname LIKE "foo";
You can also give a name to each table in the FROM clause and use something like this for simplicity:
SELECT name FROM groups g, users u, user_groups ug WHERE g.id = ug.id_groups AND ug.id_object = u.id_user AND nickname LIKE "foo";