Home » SQL & PL/SQL » SQL & PL/SQL » subquery problem
subquery problem [message #265562] Thu, 06 September 2007 11:41 Go to next message
ashish2345
Messages: 50
Registered: September 2007
Member
**********************************************
i have two tables book and issue . book contains book_name,book_id and issue contains issue_date,book_id..

i want to display the book_name,issue_date for that book

one way is equijoin or natural join like this

select book_name,issue_date from book natural join issue
or
select book_name,issue_date from book b, issue i where b.book_id=i.book_id

but when i try to get through subquery its giving wrong results

I used following subquery

select book_name,issue_date from book b, issue i where b.book_id in( select book_id from issue)


please tell how can i use simple subquery here

plz reply

thanks
ashish
Re: subquery problem [message #265563 is a reply to message #265562] Thu, 06 September 2007 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: subquery problem [message #265567 is a reply to message #265562] Thu, 06 September 2007 11:49 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

select book_name,issue_date from book b, issue i where b.book_id in( select book_id from issue)



It will cross join both table and show MxN rows. What is problem
Quote:

select book_name,issue_date from book b, issue i where b.book_id=i.book_id


this will join 2 table using book_id column.
Re: subquery problem [message #265570 is a reply to message #265567] Thu, 06 September 2007 11:52 Go to previous messageGo to next message
ashish2345
Messages: 50
Registered: September 2007
Member
thanks for replying i don't want cross join but i want the implementation using simple subquery..plz tell the subquery related
Re: subquery problem [message #265575 is a reply to message #265567] Thu, 06 September 2007 11:57 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member

I would probably write this as...

select 
       b.book_name,
       i.issue_date 
from 
     book b, 
     issue i 
where 
     b.book_id=i.book_id



It's a good idea to be specific on which table the columns are coming from. You shouldn't need to use a sub query here, unless it's a homework problem. In that case, you're on your own, because I wouldn't use a sub query for such a simple statement.

Ron
Re: subquery problem [message #265577 is a reply to message #265562] Thu, 06 September 2007 12:01 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

thanks for replying i don't want cross join but i want the implementation using simple subquery..plz tell the subquery related



If you do not want cross join then you should use equijoin or natural join or natural join.
Re: subquery problem [message #265578 is a reply to message #265575] Thu, 06 September 2007 12:01 Go to previous messageGo to next message
ashish2345
Messages: 50
Registered: September 2007
Member
Ronald Beck wrote on Thu, 06 September 2007 11:57

I would probably write this as...

select 
       b.book_name,
       i.issue_date 
from 
     book b, 
     issue i 
where 
     b.book_id=i.book_id



It's a good idea to be specific on which table the columns are coming from. You shouldn't need to use a sub query here, unless it's a homework problem. In that case, you're on your own, because I wouldn't use a sub query for such a simple statement.

Ron


thanks ron i had already tried join was just trying to clear my concept of retriving values from multiple tables using sub query but its giving cross join

thanks
Re: subquery problem [message #265579 is a reply to message #265577] Thu, 06 September 2007 12:02 Go to previous messageGo to next message
ashish2345
Messages: 50
Registered: September 2007
Member
muzahidul islam wrote on Thu, 06 September 2007 12:01
Quote:

thanks for replying i don't want cross join but i want the implementation using simple subquery..plz tell the subquery related



If you do not want cross join then you should use equijoin or natural join or natural join.


but is there no way to do it using subquery without any join
Re: subquery problem [message #265593 is a reply to message #265579] Thu, 06 September 2007 12:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Your column_names are 'wrong'. If you have a table called BOOKS, what do you think a column ID in that table would represent? Correct. A book-id.
The table_name should (normally) not be included in any column name in that table.
Re: subquery problem [message #265630 is a reply to message #265562] Thu, 06 September 2007 14:48 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

but is there no way to do it using subquery without any join


No way. Subquery at where clause always return single column value, but you want more than one cloun from subquery. You should use join
Re: subquery problem [message #265632 is a reply to message #265562] Thu, 06 September 2007 15:02 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>No way. Subquery at where clause always return single column value, but you want more than one cloun from subquery.
[sic] is suspect "cloun" is supposed to be 'column'
HUH?
12:57:47 SQL> @two-columns.sql
12:57:56 SQL> drop table test1;
Table dropped.
12:57:58 SQL> drop table test2;
Table dropped.
12:57:59 SQL> create table test1(id1 number, id2 number, id3 number);
Table created.
12:57:59 SQL> create table test2(id1 number, id2 number, id3 number);
Table created.
12:57:59 SQL> insert into test1 values(1,2,3);
1 row created.
12:57:59 SQL> insert into test1 values(2,3,4);
1 row created.
12:57:59 SQL> insert into test1 values(1,2,4);
1 row created.
12:57:59 SQL> insert into test2 values(1,2,5);
1 row created.
12:57:59 SQL> select * from test1 where (id1, id2) in (select id1, id2 from test2);

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          4
         1          2          3

12:57:59 SQL> 

Never use ALWAYS! (Never use never, too).
Above subquery seems to return more than 1 column.

[Updated on: Thu, 06 September 2007 15:03] by Moderator

Report message to a moderator

Re: subquery problem [message #265637 is a reply to message #265562] Thu, 06 September 2007 15:09 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Thanks anacedent.
i didnot know it.
Re: subquery problem [message #265640 is a reply to message #265562] Thu, 06 September 2007 15:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You don't know, what you don't know.
Re: subquery problem [message #265650 is a reply to message #265632] Thu, 06 September 2007 16:08 Go to previous message
ashish2345
Messages: 50
Registered: September 2007
Member
anacedent wrote on Thu, 06 September 2007 15:02
>No way. Subquery at where clause always return single column value, but you want more than one cloun from subquery.
[sic] is suspect "cloun" is supposed to be 'column'
HUH?
12:57:47 SQL> @two-columns.sql
12:57:56 SQL> drop table test1;
Table dropped.
12:57:58 SQL> drop table test2;
Table dropped.
12:57:59 SQL> create table test1(id1 number, id2 number, id3 number);
Table created.
12:57:59 SQL> create table test2(id1 number, id2 number, id3 number);
Table created.
12:57:59 SQL> insert into test1 values(1,2,3);
1 row created.
12:57:59 SQL> insert into test1 values(2,3,4);
1 row created.
12:57:59 SQL> insert into test1 values(1,2,4);
1 row created.
12:57:59 SQL> insert into test2 values(1,2,5);
1 row created.
12:57:59 SQL> select * from test1 where (id1, id2) in (select id1, id2 from test2);

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          4
         1          2          3

12:57:59 SQL> 

Never use ALWAYS! (Never use never, too).
Above subquery seems to return more than 1 column.


thanls a lot sir
Previous Topic: Help Please
Next Topic: Loop function between dates
Goto Forum:
  


Current Time: Thu Dec 08 06:04:10 CST 2016

Total time taken to generate the page: 0.20537 seconds