Home » SQL & PL/SQL » SQL & PL/SQL » multiple join query
icon5.gif  multiple join query [message #353654] Tue, 14 October 2008 08:46 Go to next message
hunt_times
Messages: 10
Registered: October 2008
Location: INDIA
Junior Member
have two tables
1) prod_scheme_info (prod_id_buy , prod_id_get)
2) prod_pool_info(prod_pool_id , prod_id)

and i need following output in Oracle 9i so please do help me on this because
i tried a lot to get such output but failed badly .....

so please do post the solution...

*output

PL1001 PL1001 P1001 P1001
PL1001 PL1001 P1002 P1002
PL1001 PL1001 P1003 P1003


PL1002 PL1003 P1004 P1007
PL1002 PL1003 P1005 P1008
PL1002 PL1003 P1006 P1009


Table Name and Values
prod_scheme_info

prod_id_buy prod_id_get
PL1001 PL1001
PL1002 PL1003


prod_pool_info

prod_pool_id prod_id
PL1001 P1001
PL1001 P1002
PL1001 P1003

PL1002 P1004
PL1002 P1005
PL1002 P1006


PL1003 P1007
PL1003 P1008
PL1003 P1009
Re: multiple join query [message #353655 is a reply to message #353654] Tue, 14 October 2008 08:50 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
unhelpful explanation. try again, and please follow the rules in the sticky for this forum. your output is impossible to read with a proportional font.
Re: multiple join query [message #353659 is a reply to message #353654] Tue, 14 October 2008 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In clear, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: multiple join query [message #353660 is a reply to message #353655] Tue, 14 October 2008 09:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you could tell us which table and column each of the output columns came from that ould be a big help.
Re: multiple join query [message #353674 is a reply to message #353654] Tue, 14 October 2008 10:08 Go to previous messageGo to next message
hunt_times
Messages: 10
Registered: October 2008
Location: INDIA
Junior Member
So here is the table creation code , sample data and finally formatted output

TABLE: PRODUCT_SCHEME_INFO

create table product_scheme_info 
( prod_id_buy varchar2(10) , prod_id_get varchar2(10) )

insert into table product_scheme_info (prod_id_buy , prod_id_get)
values('PL1001','PL1001')


insert into table product_scheme_info (prod_id_buy , prod_id_get)
values('PL1002','PL1003')

---------------------------
TABLE: PRODUCT_POOL_INFO

create table product_pool_info 
( prod_pool_id varchar2(10) , prod_id varchar2(10)

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1001','p1001')

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1001','p1002')

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1001','p1003')

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1002','p1004')


insert into product_pool_info (prod_pool_id , prod_id)
values('PL1002','p1005')

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1002','p1006')

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1003','p1007')

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1003','p1008')

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1003','p1009')

-------------------------------------
TABLE: PRODUCT_INFO

create table product_info 
( prod_id varchar2(10) , prod_name varchar2(10) )

insert into product_info (prod_id,prod_name)
values('p1001','orange')

insert into product_info (prod_id,prod_name)
values('p1002','lichi')

insert into product_info (prod_id,prod_name)
values('p1003','mango')

insert into product_info (prod_id,prod_name)
values('p1004','chocolate biscuit')

insert into product_info (prod_id,prod_name)
values('p1005','mix fruit biscuit')

insert into product_info (prod_id,prod_name)
values('p1006','dry fruit biscuit')

insert into product_info (prod_id,prod_name)
values('p1007','orange pickle')

insert into product_info (prod_id,prod_name)
values('p1008','mango pickle')

insert into product_info (prod_id,prod_name)
values('p1009','chili pickle')

----------------------------------
REQUIRED OUTPUT

PROD_ID_BUY |PROD_ID |PROD_NAME       |PROD_ID_GET|PROD_ID |PROD_NAME
------------------------------------------------------------------------
PL1001      |p1001   |orange          |PL1001     |p1001   |orange 
PL1001      |p1002   |lichi           |PL1001     |p1002   |lichi
PL1001      |p1003   |mango           |PL1001     |p1003   |mango
PL1002      |p1004   |choclate  biscut|PL1003     |p1007   |orange pickle
PL1002      |p1005   |mix fruit biscut|PL1003     |p1008   |lichi pickle
PL1002      |p1006   |dry fruit biscut|PL1003     |p1009   |mango pickle

*if required output is not displayed properly then i am specifying the column here

PROD_ID_BUY , PROD_ID , PROD_NAME , PROD_ID_GET , PROD_ID , PROD_NAME

[Updated on: Tue, 14 October 2008 11:00] by Moderator

Report message to a moderator

Re: multiple join query [message #353681 is a reply to message #353674] Tue, 14 October 2008 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is NOT formatted until you put code tags as I modified your post.
And statements should be ended with ';' or a '/' line.

Now you have to explain the output with words. Is it just a join between the tables?

Regards
Michel

[Updated on: Tue, 14 October 2008 11:03]

Report message to a moderator

Re: multiple join query [message #353692 is a reply to message #353681] Tue, 14 October 2008 11:38 Go to previous messageGo to next message
hunt_times
Messages: 10
Registered: October 2008
Location: INDIA
Junior Member
Sorry for the inconvenience

Well yes It is a join between tables that is

product_scheme_info , product_info and prod_pool_info

1) product_info table maintains the inventory at general stores. whatever goods come in store should have a record in table.
so here prod_id is a unique id and prod_name is a item name


2) product_scheme_info maintains the offers at general stores.
now whatever data is there in prod_info only on those data one can create "exciting offers"
so here prod_buy_id is an id that represents the range of items/products that a customer can buy

and prod_get_id is as id that represents the range of items/products that a customer can get on buying of prod_buy_id

3) prod_pool_info maintains the range of products on which a offers has to be created.

prod_pool_id represents an id. Under which we can specify range of items/products

prod_id is a reference of prod_id in product_info table


Now if i want to know that

what is current offers on all product range ?
so here product_pool_info has details related to range that is

PL1001 has 3 range of items (p1001,p1002,p1003)
PL1002 also has 3 range of items (p1004,p1005,p1006)
PL1003 also has 3 range of items (p1007,p1008,p1009)

and product_scheme_info has details that on which range of id offer is available
if you will buy (prod_buy_id)PL1001 (p1001,p1002,p1003) range of products then you will get (prod_get_id)PL1001 (p1001,p1002,p1003)products

likewise if f you will buy (prod_buy_id)PL1002(p1004,p1005,p1006) range of products then you will get (prod_get_id)PL1003 (p1007,p1008,p1009) products

so ultimately i want following thing with prod_name after joining all three table

BUY PL1001(p1001,p1002,p1003)then you will get PL1001 (p1001,p1002,p1003)
BUY PL1002(p1004,p1005,p1006) then you will get PL1003 (p1007,p1008,p1009)

*in case if you will find some sort of confusion then please do inform me

Re: multiple join query [message #353695 is a reply to message #353674] Tue, 14 October 2008 12:39 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
hunt_times wrote on Tue, 14 October 2008 11:08

-------------------------------------
TABLE: PRODUCT_INFO

[code]create table product_info
( prod_id varchar2(10) , prod_name varchar2(10) )

insert into product_info (prod_id,prod_name)
values('p1004','chocolate biscuit')



Why do you insist on faking this?

Your output is still unreadable. Please put some effort into your question so people can help.
Multiple join query [message #353808 is a reply to message #353654] Wed, 15 October 2008 03:27 Go to previous messageGo to next message
hunt_times
Messages: 10
Registered: October 2008
Location: INDIA
Junior Member

well i don't know that exactly this question is about "Why do you insist on faking this? " if it is related to data in product_info table then i want to clear that
I am actually entering those data in database.

Basically the thing is I am preparing system for general stores and supermarkets.

So that is why i need to store such data (the data which supermarket sells ) into product_info table. Now if the manager of supermarket wants to prepare an offer for example Buy 1 get 1 free , Multi buy offers etc.at that time those designed offers are need to be stored in database so now if i want to view the result on following query

1) Buy Any Biscuits worth Rs 140/- and get 10% off on Any Pickle
2) Buy Any Fruit Juices worth Rs 110/- and get 20% on any fruit juices

then i want the result of a query in following format

PROD_ID_BUY  PROD_ID  PROD_NAME         ROD_ID_GET    PROD_ID PROD_NAME
------------------------------------------------------------------------
PL1001       p1001    orange            PL1001        p1001   orange 
PL1001       p1002    lichi             PL1001        p1002   lichi
PL1001       p1003    mango             PL1001        p1003   mango
PL1002       p1004    chocolate biscuit PL1003        p1007   orange pickle
PL1002       p1005    mix fruit biscuit PL1003        p1008   lichi pickle
PL1002       p1006    dry fruit biscuit PL1003        p1009   mango pickle


so here output represents that buy any fruit that is any fruit under PL1001 and
get discount on any fruit that is again under the same id PL1001

and second record represents that buy any biscuits that is any biscuit under PL1002 and get discount on any pickles that is under PL1003 id.


well... apart from this is anything is missing let me know. And as far as
format is concern Michel Cadot has modified my code so i really thankful to Michel.

Thanks for the previous responses.

(Oracle version 9.0.1.0.0)

Re: Multiple join query [message #353869 is a reply to message #353808] Wed, 15 October 2008 07:12 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@hunt_times,
hunt_times wrote on Wed, 15 October 2008 13:57

well i don't know that exactly this question is about "Why do you insist on faking this? "



SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 15 17:48:15 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table product_info 
  2  ( prod_id varchar2(10) , prod_name varchar2(10) );

Table created.

SQL> insert into product_info (prod_id,prod_name)
  2  values('p1004','chocolate biscuit');
values('p1004','chocolate biscuit')
               *
ERROR at line 2:
ORA-12899: value too large for column "SCOTT"."PRODUCT_INFO"."PROD_NAME"
(actual: 17, maximum: 10)

I hope now you understand what joy_division quoted....

***Also the following Insert Statement is wrong,
Quote:

create table product_scheme_info 
( prod_id_buy varchar2(10) , prod_id_get varchar2(10) )

insert into table product_scheme_info (prod_id_buy , prod_id_get)
values('PL1001','PL1001')




Anyways will try to fix it and try out some queries.

Regards,
Jo

[Updated on: Wed, 15 October 2008 07:17]

Report message to a moderator

Re: multiple join query [message #353885 is a reply to message #353654] Wed, 15 October 2008 08:02 Go to previous messageGo to next message
hunt_times
Messages: 10
Registered: October 2008
Location: INDIA
Junior Member
ok i got the idea .....

so i am again posting the whole code ..... and an output ..
so please please help me out how to get the desired output

create table product_scheme_info 
( prod_id_buy varchar2(30) , prod_id_get varchar2(30) )

insert into product_scheme_info (prod_id_buy , prod_id_get)
values('PL1001','PL1001')


insert into product_scheme_info (prod_id_buy , prod_id_get)
values('PL1002','PL1003')


create table product_pool_info 
( prod_pool_id varchar2(30) , prod_id varchar2(30)

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1001','p1001')

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1001','p1002')

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1001','p1003')

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1002','p1004')


insert into product_pool_info (prod_pool_id , prod_id)
values('PL1002','p1005')

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1002','p1006')

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1003','p1007')

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1003','p1008')

insert into product_pool_info (prod_pool_id , prod_id)
values('PL1003','p1009')


create table product_info 
( prod_id varchar2(40) , prod_name varchar2(40) )

insert into product_info (prod_id,prod_name)
values('p1001','orange')

insert into product_info (prod_id,prod_name)
values('p1002','lichi')

insert into product_info (prod_id,prod_name)
values('p1003','mango')

insert into product_info (prod_id,prod_name)
values('p1004','chocolate biscuit')

insert into product_info (prod_id,prod_name)
values('p1005','mix fruit biscuit')

insert into product_info (prod_id,prod_name)
values('p1006','dry fruit biscuit')

insert into product_info (prod_id,prod_name)
values('p1007','orange pickle')

insert into product_info (prod_id,prod_name)
values('p1008','mango pickle')

insert into product_info (prod_id,prod_name)
values('p1009','chili pickle')


PROD_ID_BUY |PROD_ID |PROD_NAME       |PROD_ID_GET|PROD_ID |PROD_NAME
------------------------------------------------------------------------
PL1001      |p1001   |orange          |PL1001     |p1001   |orange 
PL1001      |p1002   |lichi           |PL1001     |p1002   |lichi
PL1001      |p1003   |mango           |PL1001     |p1003   |mango
PL1002      |p1004   |choclate  biscut|PL1003     |p1007   |orange pickle
PL1002      |p1005   |mix fruit biscut|PL1003     |p1008   |lichi pickle
PL1002      |p1006   |dry fruit biscut|PL1003     |p1009   |mango pickle
Re: multiple join query [message #353892 is a reply to message #353885] Wed, 15 October 2008 08:20 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@hunt_times,

Your required output and requirement are contradicting each other.

For Instance you said,
Quote:

if you will buy (prod_buy_id)PL1001 (p1001,p1002,p1003) range of products then you will get (prod_get_id)PL1001 (p1001,p1002,p1003)products

likewise if you will buy (prod_buy_id)PL1002(p1004,p1005,p1006) range of products then you will get (prod_get_id)PL1003 (p1007,p1008,p1009) products



And your required output is,
hunt_times wrote on Wed, 15 October 2008 18:32

PROD_ID_BUY |PROD_ID |PROD_NAME       |PROD_ID_GET|PROD_ID |PROD_NAME
------------------------------------------------------------------------
PL1001      |p1001   |orange          |PL1001     |p1001   |orange 
PL1001      |p1002   |lichi           |PL1001     |p1002   |lichi
PL1001      |p1003   |mango           |PL1001     |p1003   |mango
PL1002      |p1004   |choclate  biscut|PL1003     |p1007   |orange pickle
PL1002      |p1005   |mix fruit biscut|PL1003     |p1008   |lichi pickle
PL1002      |p1006   |dry fruit biscut|PL1003     |p1009   |mango pickle



But based on your requirement and the table info you provided, a person buying, for example a chocolate biscut can get either an orange pickle or a lichi pickle or a mango pickle right?

How come on the required output its showing a person buying a chocolate biscut is getting Orange Pickle only.

Hope you understand what I am pointing at.

Regards,
Jo
Re: multiple join query [message #353894 is a reply to message #353654] Wed, 15 October 2008 08:35 Go to previous messageGo to next message
hunt_times
Messages: 10
Registered: October 2008
Location: INDIA
Junior Member
well... Number #1 is in words only but you just concentrate on
Number #2

Number # 1:
Quote:
if you will buy (prod_buy_id)PL1001 (p1001,p1002,p1003) range of products then you will get (prod_get_id)PL1001 (p1001,p1002,p1003)products

likewise if you will buy (prod_buy_id)PL1002(p1004,p1005,p1006) range of products then you will get (prod_get_id)PL1003 (p1007,p1008,p1009) products


Number # 2:
PROD_ID_BUY |PROD_ID |PROD_NAME       |PROD_ID_GET|PROD_ID |PROD_NAME
------------------------------------------------------------------------
PL1001      |p1001   |orange          |PL1001     |p1001   |orange 
PL1001      |p1002   |lichi           |PL1001     |p1002   |lichi
PL1001      |p1003   |mango           |PL1001     |p1003   |mango
PL1002      |p1004   |choclate  biscut|PL1003     |p1007   |orange pickle
PL1002      |p1005   |mix fruit biscut|PL1003     |p1008   |lichi pickle
PL1002      |p1006   |dry fruit biscut|PL1003     |p1009   |mango pickle



As You asked:
Quote:
But based on your requirement and the table info you provided, a person buying, for example a chocolate biscut can get either an orange pickle or a lichi pickle or a mango pickle right?



Yes You are on a right track that is
a person buying, for example a chocolate biscut can get either an orange pickle or a lichi pickle or a mango pickle.

so don't think that
Quote:
How come on the required output its showing a person buying a chocolate biscut is getting Orange Pickle only.







[Updated on: Wed, 15 October 2008 08:39] by Moderator

Report message to a moderator

Re: multiple join query [message #353904 is a reply to message #353894] Wed, 15 October 2008 09:34 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Thats it... I am confused. http://img2.mysmiley.net/imgs/smile/confused/confused0024.gif

Now I am also curious for the answers you will get.http://img2.mysmiley.net/imgs/smile/evilgrin/evilgrin0010.gif

Regards,
Jo
Re: multiple join query [message #353914 is a reply to message #353654] Wed, 15 October 2008 10:33 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Well I had a bash at this and the closest I could get was this

PROD_ID_BU PROD_ID    PROD_NAME            PROD_ID_GE PROD_ID    PROD_NAME
---------- ---------- -------------------- ---------- ---------- ---------------
PL1001     p1001      orange               PL1001     p1001      orange
PL1001     p1001      orange               PL1001     p1002      lichi
PL1001     p1001      orange               PL1001     p1003      mango
PL1001     p1002      lichi                PL1001     p1001      orange
PL1001     p1002      lichi                PL1001     p1003      mango
PL1001     p1002      lichi                PL1001     p1002      lichi
PL1001     p1003      mango                PL1001     p1001      orange
PL1001     p1003      mango                PL1001     p1003      mango
PL1001     p1003      mango                PL1001     p1002      lichi
PL1002     p1004      chocolate biscuit    PL1003     p1007      orange pickle
PL1002     p1004      chocolate biscuit    PL1003     p1009      chili pickle
PL1002     p1004      chocolate biscuit    PL1003     p1008      mango pickle
PL1002     p1005      mix fruit biscuit    PL1003     p1007      orange pickle
PL1002     p1005      mix fruit biscuit    PL1003     p1009      chili pickle
PL1002     p1005      mix fruit biscuit    PL1003     p1008      mango pickle
PL1002     p1006      dry fruit biscuit    PL1003     p1007      orange pickle
PL1002     p1006      dry fruit biscuit    PL1003     p1009      chili pickle
PL1002     p1006      dry fruit biscuit    PL1003     p1008      mango pickle


Is this want you want or does it have to be as per your Number # 2 output?
Re: multiple join query [message #353928 is a reply to message #353654] Wed, 15 October 2008 11:58 Go to previous messageGo to next message
hunt_times
Messages: 10
Registered: October 2008
Location: INDIA
Junior Member
hey Scorpio biker Thanks a lot for trying but dear i have also tried to this level but no this not the Number #2 output which i
actually want .... hope you would filter out more...

and hey dear joice john you are on a right track so don;t get
confused just go 4 it once again...
Re: multiple join query [message #353930 is a reply to message #353928] Wed, 15 October 2008 12:20 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@hunt_times,

I had reached the same point as Scorpio_biker. Thats why I raised the question,
Quote:

How come on the required output its showing a person buying a chocolate biscut is getting Orange Pickle only.


and you replied as,
Quote:

so don't think that
Quote:

How come on the required output its showing a person buying a chocolate biscut is getting Orange Pickle only.



That got me confused. Can you prove your point with the required output you have given? I suggest you to think over your requirement once again with the points I asked you previously. I will get back to you if I find any useful information.

Regards,
Jo
Re: multiple join query [message #353972 is a reply to message #353654] Wed, 15 October 2008 23:24 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

What will happen / should be the output IF


Quote:
you will buy (prod_buy_id)PL1002(p1004,p1005,p1006) range of products then you will get (prod_get_id)PL1003 (p1007,p1008) products



Or

Quote:
you will buy (prod_buy_id)PL1002(p1004,p1005) range of products then you will get (prod_get_id)PL1003 (p1007,p1008,p1009) products


Thumbs Up
Rajuvan.
Re: multiple join query [message #353981 is a reply to message #353654] Thu, 16 October 2008 00:11 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Anyway

SELECT BUYI.PROD_ID_BUY ,BUYI.PROD_ID, 
       BUYI.PROD_NAME PROD_NAME_BUY ,GETI.PROD_ID_GET,
       GETI.PROD_ID ,GETI.PROD_NAME PROD_NAME_GET
 FROM 
(
    SELECT PSI.PROD_ID_BUY  ,PPI.PROD_ID  , PPI.PROD_NAME,
           ROW_NUMBER ()  OVER ( PARTITION BY PSI.PROD_ID_BUY  ORDER BY PPI.PROD_ID ) rn
    FROM   PRODUCT_SCHEME_INFO PSI,
           ( SELECT PPI.*,PI.PROD_NAME  
             FROM  PRODUCT_POOL_INFO PPI,
                   PRODUCT_INFO PI 
                   WHERE PI.PROD_ID = PPI.PROD_ID ) PPI
    WHERE  PSI.PROD_ID_BUY = PPI.PROD_POOL_ID ) BUYI ,
    (SELECT PSI.PROD_ID_BUY  ,PSI.PROD_ID_GET ,PPI.PROD_ID  , PPI.PROD_NAME,
           ROW_NUMBER ()  OVER ( PARTITION BY PSI.PROD_ID_BUY  ORDER BY PPI.PROD_ID )RN
    FROM PRODUCT_SCHEME_INFO PSI,
         ( SELECT PPI.*,PI.PROD_NAME  
           FROM  PRODUCT_POOL_INFO PPI,
                 PRODUCT_INFO PI 
                 WHERE PI.PROD_ID = PPI.PROD_ID ) PPI
    WHERE PSI.PROD_ID_GET = PPI.PROD_POOL_ID ) GETI
 WHERE BUYI.PROD_ID_BUY  = GETI.PROD_ID_BUY 
 AND   BUYI.RN           = GETI.RN 


May give you a good start you are looking for

Thumbs Up
Rajuvan.
Re: multiple join query [message #354057 is a reply to message #353654] Thu, 16 October 2008 04:47 Go to previous messageGo to next message
hunt_times
Messages: 10
Registered: October 2008
Location: INDIA
Junior Member
hi..rajavu1 ... .Thanks a lot for .... replying ya this query works fine but could you just create some simplified version of that query please?
Re: multiple join query [message #354059 is a reply to message #353654] Thu, 16 October 2008 04:57 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Please carry on with my starting point .

All the best .

Thumbs Up
Rajuvan.
Re: multiple join query [message #354104 is a reply to message #353654] Thu, 16 October 2008 07:50 Go to previous messageGo to next message
hunt_times
Messages: 10
Registered: October 2008
Location: INDIA
Junior Member
HI rajavu1 , Could You explain You Query little ..... Smile
Re: multiple join query [message #354750 is a reply to message #353654] Tue, 21 October 2008 02:05 Go to previous messageGo to next message
hunt_times
Messages: 10
Registered: October 2008
Location: INDIA
Junior Member
if i will add one row in

product_info and one row in product_pool_info then this row would not be shown in a query suggested by rajauv1

insert into product_info (prod_id,prod_name)
values('p1010','garlic pickle')


insert into product_pool_info (prod_pool_id , prod_id)
values('PL1003','p1010')



Above row indicates that thare are mover verity of pickles that customer can get on purchase of biscuits ....

so as i want this row in desired result , please help me how to do that ..

Thanking you
Re: multiple join query [message #354758 is a reply to message #353654] Tue, 21 October 2008 02:40 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Then Use FULL OUTER JOIN .

I asked this question earlier , but you never responded .


SELECT BUYI.PROD_ID_BUY ,BUYI.PROD_ID, 
       BUYI.PROD_NAME PROD_NAME_BUY ,GETI.PROD_ID_GET,
       GETI.PROD_ID ,GETI.PROD_NAME PROD_NAME_GET
 FROM 
(
    SELECT PSI.PROD_ID_BUY  ,PPI.PROD_ID  , PPI.PROD_NAME,
           ROW_NUMBER ()  OVER ( PARTITION BY PSI.PROD_ID_BUY  ORDER BY PPI.PROD_ID ) rn
    FROM   PRODUCT_SCHEME_INFO PSI,
           ( SELECT PPI.*,PI.PROD_NAME  
             FROM  PRODUCT_POOL_INFO PPI,
                   PRODUCT_INFO PI 
                   WHERE PI.PROD_ID = PPI.PROD_ID ) PPI
    WHERE  PSI.PROD_ID_BUY = PPI.PROD_POOL_ID ) BUYI 
  FULL OUTER JOIN 
    (SELECT PSI.PROD_ID_BUY  ,PSI.PROD_ID_GET ,PPI.PROD_ID  , PPI.PROD_NAME,
           ROW_NUMBER ()  OVER ( PARTITION BY PSI.PROD_ID_BUY  ORDER BY PPI.PROD_ID )RN
    FROM PRODUCT_SCHEME_INFO PSI,
         ( SELECT PPI.*,PI.PROD_NAME  
           FROM  PRODUCT_POOL_INFO PPI,
                 PRODUCT_INFO PI 
                 WHERE PI.PROD_ID = PPI.PROD_ID ) PPI
    WHERE PSI.PROD_ID_GET = PPI.PROD_POOL_ID ) GETI
 ON  BUYI.PROD_ID_BUY  = GETI.PROD_ID_BUY 
 AND   BUYI.RN           = GETI.RN 


Thumbs Up
Rajuvan.
Previous Topic: Passing key value pairs for :new from trigger to procedure
Next Topic: Advanced partitioning question
Goto Forum:
  


Current Time: Fri Dec 02 16:24:20 CST 2016

Total time taken to generate the page: 0.38085 seconds