Home » SQL & PL/SQL » SQL & PL/SQL » selection from more than two tables
selection from more than two tables [message #237639] Tue, 15 May 2007 05:21 Go to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hello to ORAFAQ members,
Please find out the solution for the following
problem if possible.I am designing a website and I have the following tables in my database[the
columns are mentioned in the braces]
•LOGIN TABLE [login(primary key), password,password_hint]
•USERTABLE[login, u_image_url, socialstatus,education, address, contact]
•FRIENDSTABLE[friend_login,friend_name,nick_name,f_image_url,u_scraps,login]
•VISITORSTABLE[visitor_login, visitor_name,visitor_image_url,v_scraps, login]



--login is a common column in all these tables.

Requirement:

I want to retrieve
1.login from LOGINTABLE
2.u_image_url from USERTABLE
3.friend_login, friend_name, f_image_url, u_scraps from FRIENDSTABLE
4.visitor_login, visitor_name, visitor_image_url,
v_scraps from VISITORSTABLE.




All these values need to be retrieved by using SQL,
this is similar to the Orkut home page scenario.
You can make small changes to the tables if required
but my requirement must not be changed.
The scraps of friends and visitors should be in the
same table and this is the main requirement.
Thanking you in anticipation,
VamsiKGummadi.

Report message to a moderator

Re: selection from more than two tables [message #237641 is a reply to message #237639] Tue, 15 May 2007 05:28 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
You can simply join all those tables on the LOGIN field, don't you?

MHE

Report message to a moderator

Re: selection from more than two tables [message #237648 is a reply to message #237639] Tue, 15 May 2007 06:22 Go to previous messageGo to next message
apps.suresh
Messages: 15
Registered: October 2006
Junior Member
may be this helps you...


this is the query to retrieve the date u want....

select login
, ut.u_image_url
, ft.friendlogin
, ft.friendname
, ft.f_image_url
, ft.u_scraps
, vt.visitorlogin
, vt.visitorname
, vt.visitor_image_url
, vt.v_scraps
from logintable lt
, usertable ut
, friendstable ft
, visitorstable vt
where lt.login = ut.login
and lt.login = ft.login
and lt.login = vt.login
and lt.login like '12345'


regards
suresh

Report message to a moderator

Re: selection from more than two tables [message #237650 is a reply to message #237648] Tue, 15 May 2007 06:39 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Maaher wrote on Tue, 15 May 2007 12:28
You can simply join all those tables on the LOGIN field, don't you?

apps.suresh wrote on Tue, 15 May 2007 13:22
may be this helps you......
<snip>
and lt.login like '12345'

The LIKE operator will be appreciated by the optimizer I think.

MHE

[Updated on: Tue, 15 May 2007 06:40]

Report message to a moderator

Re: selection from more than two tables [message #237664 is a reply to message #237650] Tue, 15 May 2007 07:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Don't worry Maarten.
The optimizer recognizes such silly things and convert them to =.

Regards
Michel

Report message to a moderator

Re: selection from more than two tables [message #237670 is a reply to message #237664] Tue, 15 May 2007 07:46 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Michel Cadot wrote on Tue, 15 May 2007 14:36
Don't worry Maarten.
The optimizer recognizes such silly things and convert them to =.

Regards
Michel


Are you sure? 
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL>
SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM   departments
  4  WHERE  department_id = 100
  5  /

Explained.

SQL>
SQL>
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY)
  3  /
Plan hash value: 4024094692

--------------------------------------------------------------------------------
-----------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|
 Time     |
--------------------------------------------------------------------------------
-----------
|   0 | SELECT STATEMENT            |             |     1 |    20 |     1   (0)|
 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    20 |     1   (0)|
 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)|
 00:00:01 |
--------------------------------------------------------------------------------
-----------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPARTMENT_ID"=100)

14 rows selected.

SQL>
SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM   departments
  4  WHERE  department_id LIKE 100
  5  /

Explained.

SQL>
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY)
  3  /
Plan hash value: 4167016233

--------------------------------------------------------------------------------
-
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
|   0 | SELECT STATEMENT  |             |     1 |    20 |     3   (0)| 00:00:01
|
|*  1 |  TABLE ACCESS FULL| DEPARTMENTS |     1 |    20 |     3   (0)| 00:00:01
|
--------------------------------------------------------------------------------
-

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_CHAR("DEPARTMENT_ID") LIKE '100')

13 rows selected.

SQL>
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY)
  3  /
Plan hash value: 4167016233

--------------------------------------------------------------------------------
-
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
|   0 | SELECT STATEMENT  |             |     1 |    20 |     3   (0)| 00:00:01
|
|*  1 |  TABLE ACCESS FULL| DEPARTMENTS |     1 |    20 |     3   (0)| 00:00:01
|
--------------------------------------------------------------------------------
-

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_CHAR("DEPARTMENT_ID") LIKE '100')

13 rows selected.


MHE

Report message to a moderator

Re: selection from more than two tables [message #237672 is a reply to message #237639] Tue, 15 May 2007 07:48 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
IMHO - if login is numeric column then LIKE will actually perform TO_CHAR(login) and disable possible index usage.

Michael

Report message to a moderator

Re: selection from more than two tables [message #237673 is a reply to message #237670] Tue, 15 May 2007 07:48 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
hehe, not fair!
Like is a string-operator, so an implicit datatype conversion is done.
It is not and lt.login like '12345' vs and lt.login = '12345', but whether or not lt.login is really a string.

Report message to a moderator

Re: selection from more than two tables [message #237682 is a reply to message #237673] Tue, 15 May 2007 08:11 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Very Happy I couldn't resist.

MHE

Report message to a moderator

Re: selection from more than two tables [message #237841 is a reply to message #237641] Tue, 15 May 2007 23:46 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hello all,
Thank you for the help but all the efforts went in vain. I am uanble to retrieve the data as required .Still there is duplication of values in some of the columns which is to be avoided.I have tried all your hints and queries.Some of you suggested to use joins if necessary, can you please type the code for me.
I thank you all for the trials you have made to help me to figure out the problem.
Regards,
VamsiK Gummadi.

Report message to a moderator

Re: selection from more than two tables [message #237866 is a reply to message #237841] Wed, 16 May 2007 00:57 Go to previous messageGo to next message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
vamsikgummadi wrote on Wed, 16 May 2007 05:46
Thank you for the help but all the efforts went in vain. I am uanble to retrieve the data as required .Still there is duplication of values in some of the columns which is to be avoided.I have tried all your hints and queries.Some of you suggested to use joins if necessary, can you please type the code for me.


"Joins if necessary"? Surely you decide whether or not joins are necessary besed on your data model? What are the unique and foreign key definitions? Some same data would help as well (in [code] tags, please.) Anyway, some code has already been posted. All we know is there is "some duplication in some of the columns". How can anyone know what to change in the query based on that information?

vamsikgummadi wrote
All these values need to be retrieved by using SQL, this is similar to the Orkut home page scenario. You can make small changes to the tables if required
but my requirement must not be changed. The scraps of friends and visitors should be in the same table and this is the main requirement.


I have no idea what the Orkut home page scenario is, or what you mean by "scraps of friends and visitors", or how else you could retrieve anything from Oracle other than by using SQL.

Can we remove the word "TABLE" from all of the table names?

[Updated on: Wed, 16 May 2007 01:01]

Report message to a moderator

Re: selection from more than two tables [message #238005 is a reply to message #237866] Wed, 16 May 2007 05:58 Go to previous message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hello Mr.William Robertson,
Yes, you can remove the word table from all these table names for convenience.The main purpose of the query is retrieve scraps from visitors and friends in a single table or view by suppling an input.The input value is something similar to login.I hope I am clear with my doubt .Please get bak if you need some other clarification regarding this doubt.
Vamsi K Gummadi.

Report message to a moderator

Previous Topic: instr help
Next Topic: number to word conversion.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 03 18:51:19 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.