-
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
-
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
-
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(+)
-
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
-
Hi,
for better performance you can move userid condition in the subquery.
Vinitha's solution looks more straightforward.
Ales
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|