Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 01-10-2009
Member
 
Join Date: Aug 2009
Posts: 59
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?
Reply With Quote
  #2  
Old 01-10-2009
Member
 
Join Date: May 2008
Posts: 2,293
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"
Reply With Quote
  #3  
Old 01-10-2009
Member
 
Join Date: Aug 2009
Posts: 59
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
Reply With Quote
  #4  
Old 01-10-2009
Member
 
Join Date: Nov 2008
Posts: 1,221
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";
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Problem with construction of a query"
Thread Thread Starter Forum Replies Last Post
Query cache denied problem Alibamu Software Development 6 19-06-2010 12:35 AM
Regex problem in construction Miles Runner Software Development 5 04-02-2010 03:59 AM
Problem of nested query MAGAR Software Development 4 09-12-2009 09:55 PM
Coldfusion query problem AZUL Software Development 3 26-11-2009 07:55 PM
Problem of SQL query Sonic Software Development 5 18-12-2008 12:28 PM


All times are GMT +5.5. The time now is 05:59 AM.