Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2015
    Posts
    2

    Answered: Subquery returned more than 1 value problem

    Hello

    I know there is a post with same name

    i am trying to run Select query to get "application name" and "role" for each user in the DB
    problem is that each user has more than one App that he is useing and for each App he can have more than one role

    My code is:

    Code:
    select top 100 
    	username,
    	(Select b.name
    		from application b
    		inner join userroleapplication c
    		on b.applicationID = c.applicationID ) Application,
    	(select name from role d, userroleapplication gr 
    		where gr.roleID = d.roleID and gr.userID = a.userID) role
    
    from users a
    where username in
    (
    'username'
    
    )
    i know where is the problem but i cant figure how to fix this

    What i need as a output

    Username | Application | Role

    "user1" | "App1" | "Role1"
    "user1" | "App1" | "Role2"
    "user1" | "App2" | "Role1"
    "user1" | "App3" | "Role1"
    "user2" | "App1" | "Role1"
    "user2" | "App3" | "Role1"
    etc.

    Thanks

  2. Best Answer
    Posted by gvee

    "
    Code:
    SELECT users.username
         , application.name As application_name
         , roles.name As role_name
    FROM   users
     INNER
      JOIN userroleapplication
        ON userroleapplication.userid = users.userid
     INNER
      JOIN role
        ON role.roleid = userroleapplication.roleid
     INNER
      JOIN application
        ON application.applicationid = userroleapplication.applicationid
    WHERE  users.username IN ('gvee')
    ?"


  3. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT users.username
         , application.name As application_name
         , roles.name As role_name
    FROM   users
     INNER
      JOIN userroleapplication
        ON userroleapplication.userid = users.userid
     INNER
      JOIN role
        ON role.roleid = userroleapplication.roleid
     INNER
      JOIN application
        ON application.applicationid = userroleapplication.applicationid
    WHERE  users.username IN ('gvee')
    ?
    George
    Home | Blog

  4. #3
    Join Date
    Mar 2015
    Posts
    2

    Smile It woked

    Thanks alot

    you just saved the day

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •