problems in a query with joins
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: problems in a query with joins

  1. #1
    Join Date
    Dec 2000
    Posts
    95

    Question

    I have problem. please take a look at the tables,the queries.


    "Users" Table (This table provides the user information)

    userID----FIRSTNAME---LASTNAME
    --------- ----------- ------------
    user1-----------Jim-------Carry
    user2-----------Tom-------lee
    user3-----------sunny-----boo
    user4-----------mary------mall

    course_info (this table contains the course details)

    coursecode---- coursename
    ------------- ----------------
    c1----------------- course1
    c2----------------- course2
    c3----------------- course3


    user_score (This table provides the user scores of different courses)

    USERID----coursecode-----STATUS
    -------- ---------- ----------
    user1------c1-----in-progress
    user1------c3-----complete
    user3------c1-----in-progress
    user3------c3-----in-progress


    I have a problem here observe the result of this query and the below query.

    select u.userid,u.firstname,us.status from
    users u,user_scores us,course_info ci
    where u.userid='user1' and
    u.userid=us.userid(+) and
    ci.coursecode in ('c1','c2',c3') and
    (ci.coursecode=us.coursecode or us.coursecode is null);

    userid --- firstname --- STATUS----------course
    -------- ------------ ---------- -------------------
    user1---------------jim--in-progress--- c1
    user1---------------jim--complete-------c3

    2 rows selected


    But I want result like follows

    userid --- firstname --- STATUS----------course
    -------- ------------ ---------- -------------------
    user1---------------jim--in-progress--- c1
    user1---------------jim--- ------------c2
    user1---------------jim--complete-------c3

    3 rows selected



    will take another instance for user2


    select u.userid,u.firstname,us.status from
    users u,user_scores us,course_info ci
    where u.userid='user2' and
    u.userid=us.userid(+) and
    ci.coursecode in ('c1','c2',c3') and
    (ci.coursecode=us.coursecode or us.coursecode is null);

    0 rows selected

    Because it user2 doesn't have any record in user_scores table.

    But I want the result like follows

    userid --- firstname --- STATUS----------course
    -------- ------------ ---------- -------------------
    user1---------------jim--- ----------- c1
    user1---------------jim--- ------------c2
    user1---------------jim--- ------------c3

    3 rows selected



    I think u got the difference in the both the query results.

    it has to show the info of user1for all the courses even though the user record doesn't exist in the user_scores table and even though the user and all courses combination doesn't exist in the user_scores table.


    I think we can't use join when we use the "in" function.

    what changes i have to do to get the above result ?

    I am using Oracle8i(8.1.7).

    thank you for any help

    Srinivas M

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    I believe a subquery could help you. It makes a cartesian product (= all combinations) of users and courses. Then you can outer-join the table user_scores with the result of that:
    Code:
    select uc.userid, uc.firstname, us.status, uc.coursecode
    from user_scores us,
    (
      select u.userid, u.firstname, ci.coursecode
        from users u, course_info ci
       where ci.coursecode in ('c1','c2','c3')
    ) uc
    where uc.userid = us.userid (+)
      and uc.coursecode = us.coursecode (+)
      and uc.userid = 'user1'
    HTH,
    Ales

  3. #3
    Join Date
    Feb 2002
    Posts
    2
    Hope this query solves your problem

    select u.userid,u.firstname,us.status from
    users u,user_score us,course_info ci
    where u.userid='user1' and
    us.userid(+) = 'user1' and
    ci.coursecode=us.coursecode(+)

  4. #4
    Join Date
    Dec 2000
    Posts
    95
    Hi ,

    If we follow this way, i think isn't it a performance issue. Beacuse I have 3000 users and 3 courses, then the result of the sub query would be 3000*3=9000 records. Isn't it a performance issue.


    Thanks for any help


    Srinivas M

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hi,
    for better performance you can move userid condition in the subquery.
    Vinitha's solution looks more straightforward.
    Ales

  6. #6
    Join Date
    Dec 2000
    Posts
    95

    Smile

    Hi Vinitha,

    I have tried with the query which u have suggested. its working fine.

    your solution is simply great.


    Thank you

    Srinivas M
    (srinivasm_21@yahoo.co.uk)

Posting Permissions

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


Click Here to Expand Forum to Full Width