Home » SQL & PL/SQL » SQL & PL/SQL » SQL- Outer join 3 tables (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi, Windows XP)
SQL- Outer join 3 tables [message #575490] Wed, 23 January 2013 11:32 Go to next message
ao123
Messages: 12
Registered: November 2011
Junior Member
Hello,

I'm wondering what part of this query is wrong because obviously I'm not getting the desired results.

select a.iss_id,C.ISSR_ID
from ft_t_isid a left outer join ft_t_issu b on a.INSTR_ID=b.INSTR_ID
left outer join ft_t_irid c on b.INSTR_ISSR_ID=c.INSTR_ISSR_ID
and a.ISS_ID in ('CA13606ZDD20',
'CA780086AP98',
'AU3CB0176410',
'CND100003HM1',
'JP320045P8C8',
'CND100003CY7',
'TW0002547403',
'TW000B903ST4',
'XS0543801350')

I know that the problem is in the joins. I expect to get 9 rows of result but I get a lot more and they are not even what I'm looking for. Appreciate your help.

Afshin
Re: SQL- Outer join 3 tables [message #575491 is a reply to message #575490] Wed, 23 January 2013 11:35 Go to previous messageGo to next message
BlackSwan
Messages: 22707
Registered: January 2009
Senior Member
>I'm not getting the desired results.
How are we to know exactly what are the "desired results"?

since we don't have your tables or data, we can't run, test or improve posted SQL.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: SQL- Outer join 3 tables [message #575492 is a reply to message #575490] Wed, 23 January 2013 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 58907
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your first topic:

Michel Cadot wrote on Mon, 21 November 2011 19:23
...
If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Re: SQL- Outer join 3 tables [message #575494 is a reply to message #575492] Wed, 23 January 2013 12:00 Go to previous messageGo to next message
ao123
Messages: 12
Registered: November 2011
Junior Member
Thank you BlackSwan and Michel,

As a database user with read only access to our database, I am not able to create test case tables in the database as suggested in the guidelines. What I can say as desired results is I expect to see something like below:

ISS_ID              ISSR_ID
CA13606ZDD20       
CA780086AP98        2345
AU3CB0176410        4452
CND100003HM1       
JP320045P8C8        556468 
CND100003CY7
TW0002547403
TW000B903ST4        278978
XS0543801350



But I get thousands of ISS_IDs that are not even in my query and none of them has an ISSR_ID. Hope this complies a little more with the guidelines. Thank you again.

SELECT a.iss_id, 
       C.issr_id 
FROM   ft_t_isid a 
       LEFT OUTER JOIN ft_t_issu b 
                    ON a.instr_id = b.instr_id 
       LEFT OUTER JOIN ft_t_irid c 
                    ON b.instr_issr_id = c.instr_issr_id 
                       AND a.iss_id IN ( 'CA13606ZDD20', 'CA780086AP98', 
                                         'AU3CB0176410' 
                                         , 'CND100003HM1', 
                                         'JP320045P8C8', 'CND100003CY7', 
                                         'TW0002547403' 
                                         , 'TW000B903ST4' 
                                         , 
                                             'XS0543801350' ) 



Afshin
Re: SQL- Outer join 3 tables [message #575500 is a reply to message #575494] Wed, 23 January 2013 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58907
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 23 January 2013 18:42
From your first topic:

Michel Cadot wrote on Mon, 21 November 2011 19:23
...
If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel


Re: SQL- Outer join 3 tables [message #575501 is a reply to message #575500] Wed, 23 January 2013 12:13 Go to previous messageGo to next message
BlackSwan
Messages: 22707
Registered: January 2009
Senior Member
Since we have no tables or data to work with, no solution is possible.

You're On Your Own (YOYO)!

Re: SQL- Outer join 3 tables [message #575502 is a reply to message #575501] Wed, 23 January 2013 12:21 Go to previous messageGo to next message
ao123
Messages: 12
Registered: November 2011
Junior Member
Wow! What a convenient response! In light of all the fact that you make users follow all of your guidelines before you help them, perhaps it is useful to indicate in your guidelines that you can only support those who have full access to their database and people with less access don't waste their time here. I don't see anythign like that in your guidelines.

I'm pretty sure a competent person can answer my question by just looking at my query and without the need to look at any tables or data. And fyi, I am closing my account in this website as it is a waste of time.


Regards,
Afshin
Re: SQL- Outer join 3 tables [message #575503 is a reply to message #575502] Wed, 23 January 2013 12:25 Go to previous messageGo to next message
BlackSwan
Messages: 22707
Registered: January 2009
Senior Member
>I'm pretty sure a competent person can answer my question by just looking at my query and without the need to look at any tables or data.

Are you admitting that you are not a competent person?
Re: SQL- Outer join 3 tables [message #575504 is a reply to message #575502] Wed, 23 January 2013 12:37 Go to previous messageGo to next message
Michel Cadot
Messages: 58907
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ao123 wrote on Wed, 23 January 2013 19:21
Wow! What a convenient response! In light of all the fact that you make users follow all of your guidelines before you help them, perhaps it is useful to indicate in your guidelines that you can only support those who have full access to their database and people with less access don't waste their time here. I don't see anythign like that in your guidelines.

I'm pretty sure a competent person can answer my question by just looking at my query and without the need to look at any tables or data. And fyi, I am closing my account in this website as it is a waste of time.


Regards,
Afshin


But should we make some effort to help you when you don't want to make to help us to help you?
As there are many people that ask for help I choose the ones that I will help: the one that make me the easiest work to do to help.
If you don't agree, you are right this site is not for you.
Every place you will go will have its rules, don't think they will change them for yours just because you want it.
You should be less arrogant when you ask for help.
You are leaving... we won't miss you and we will have more time to help valuable people that need it.

Regards
Michel


[Updated on: Wed, 23 January 2013 12:38]

Report message to a moderator

Re: SQL- Outer join 3 tables [message #575505 is a reply to message #575503] Wed, 23 January 2013 12:40 Go to previous messageGo to next message
ao123
Messages: 12
Registered: November 2011
Junior Member
First things first: To take the time to pick a fight of words with a member proves how low the level of credibility and professionalism of this forum is. Second: No, I am saying that you guys are not competent. I don't need to be an expert in sql or databases. Please don't try to twist my word. Instead, spend your time doing something productive such as improving your guidelines and your database skills.

Thankfully there are thousands of other sources that do a much better job that you guys.

Adios
Re: SQL- Outer join 3 tables [message #575506 is a reply to message #575505] Wed, 23 January 2013 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 58907
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

Re: SQL- Outer join 3 tables [message #575511 is a reply to message #575490] Wed, 23 January 2013 14:29 Go to previous messageGo to next message
sqlsatya
Messages: 10
Registered: December 2012
Junior Member
There should not be any arrogance and fighting....
When asking for help or giving help...

you may try this

SELECT a.iss_id,
C.issr_id
FROM ft_t_isid a
LEFT OUTER JOIN ft_t_issu b
ON a.instr_id = b.instr_id
LEFT OUTER JOIN ft_t_irid c
ON b.instr_issr_id = c.instr_issr_id
WHERE a.iss_id IN ( 'CA13606ZDD20', 'CA780086AP98',
'AU3CB0176410'
, 'CND100003HM1',
'JP320045P8C8', 'CND100003CY7',
'TW0002547403'
, 'TW000B903ST4'
,
'XS0543801350' );
Re: SQL- Outer join 3 tables [message #575512 is a reply to message #575511] Wed, 23 January 2013 14:36 Go to previous messageGo to next message
Michel Cadot
Messages: 58907
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But refusing, like you here, to post correctly is just a mark of contempt.

Regards
Michel
Re: SQL- Outer join 3 tables [message #575515 is a reply to message #575494] Wed, 23 January 2013 15:14 Go to previous messageGo to next message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
ao123 wrote on Wed, 23 January 2013 13:00
As a database user with read only access to our database, I am not able to create test case tables in the database as suggested in the guidelines.


I do not have access to you database either, but that doesn't prevent me from creating a test case.
This is what the users here are looking for:
create table ft_ti_isd (
col1 varchar2(20),
col2 date,
etc.)
/

insert into ft_ti_isd (col1, col2, ...) values ('A', sysdate, etc.)
/


so that someone here can run the test case to create the tables, insert data and start coding against it.

No database access is necessary to create a test case.

Re: SQL- Outer join 3 tables [message #575516 is a reply to message #575515] Wed, 23 January 2013 15:35 Go to previous messageGo to next message
ao123
Messages: 12
Registered: November 2011
Junior Member
Thank you all for your input. As a business end user of our database with very limited technical/database knowledge, and knowing that I only have read access to the database, my understanding is that I cannot run Create Table sql in my environment. Joy_division, if you are suggesting that I should simply write up a text to mimic the Create Table, Insert into , and so on sql to show what I need, I'm pretty sure I will be very prone to typos and when other users try to run it, it most likely will fail. Unless I am missing your point.

Please note that I am not trying to be lazy and not do the leg work. I just don't have enough previlege or don't know how.

Thank you all again,
Afshin

[Updated on: Wed, 23 January 2013 15:37]

Report message to a moderator

Re: SQL- Outer join 3 tables [message #575517 is a reply to message #575511] Wed, 23 January 2013 15:42 Go to previous messageGo to next message
ao123
Messages: 12
Registered: November 2011
Junior Member
Thank you very much sqlsatya. However, I still only get the rows back where the condition is met. Meaning I do not get the rows where ISSR_ID is null. Any idea is greatly appreciated.
Re: SQL- Outer join 3 tables [message #575520 is a reply to message #575517] Wed, 23 January 2013 16:36 Go to previous messageGo to next message
John Watson
Messages: 4505
Registered: January 2010
Location: Global Village
Senior Member
Can you re-describe what it is that you want? In your second post you sayQuote:
But I get thousands of ISS_IDs that are not even in my query and none of them has an ISSR_ID.
But later you sayQuote:
I still only get the rows back where the condition is met. Meaning I do not get the rows where ISSR_ID is null.
Isn;t this contradictory? Or have I mis-understood?
Re: SQL- Outer join 3 tables [message #575521 is a reply to message #575520] Wed, 23 January 2013 17:01 Go to previous messageGo to next message
BlackSwan
Messages: 22707
Registered: January 2009
Senior Member
SQL> set long 32000
SQL> select dbms_metadata.get_ddl('TABLE','EMP','USER1') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','EMP','USER1')
--------------------------------------------------------------------------------

  CREATE TABLE "USER1"."EMP"
   (    "EMPLOYEE_ID" NUMBER(6,0),
        "FIRST_NAME" VARCHAR2(20),
        "LAST_NAME" VARCHAR2(25) NOT NULL ENABLE,
        "EMAIL" VARCHAR2(25) NOT NULL ENABLE,
        "PHONE_NUMBER" VARCHAR2(20),
        "HIRE_DATE" DATE NOT NULL ENABLE,
        "JOB_ID" VARCHAR2(10) NOT NULL ENABLE,
        "SALARY" NUMBER(8,2),
        "COMMISSION_PCT" NUMBER(2,2),

DBMS_METADATA.GET_DDL('TABLE','EMP','USER1')
--------------------------------------------------------------------------------
        "MANAGER_ID" NUMBER(6,0),
        "DEPARTMENT_ID" NUMBER(4,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"



Re: SQL- Outer join 3 tables [message #575530 is a reply to message #575520] Wed, 23 January 2013 20:39 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
@John Watson: There is the difference between the original query and the query from sqlsatya.
In the original one, the condition on A.ISS_ID is in JOIN clause with C table, so all rows from tables A and B (their left join) are shown; in C.ISSR_ID will be NULL when this condition is not met.
In the one sqlsatya provided, the condition on A.ISS_ID is in WHERE clause, so it filters rows with different ISS_ID in A from the result set.

You may check this demonstration in scott schema (based on two tables only, but it would be similar for more tables):
SQL> select dept.deptno, emp.empno
  2  from dept left join emp on emp.deptno = dept.deptno
  3    and dept.deptno=10;

    DEPTNO      EMPNO
---------- ----------
        10       7782
        10       7839
        10       7934
        20
        30
        40

6 rows selected.

SQL> select dept.deptno, emp.empno
  2  from dept left join emp on emp.deptno = dept.deptno
  3  where dept.deptno=10;

    DEPTNO      EMPNO
---------- ----------
        10       7782
        10       7839
        10       7934

As original poster did not describe, what is the required result (except complaining about number of rows returned), it is impossible to propose anything except (more or less) educated guesses.
Re: SQL- Outer join 3 tables [message #575532 is a reply to message #575516] Wed, 23 January 2013 21:05 Go to previous message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
@ao123: even with only read access to the database: you may create a test case by including some sample data (it does not have to be content of all tables, just a few rows demonstrating the issue will suffice) directly into the query.
with ft_t_isid as ( select 'CA13606ZDD20' iss_id, 1 instr_id from dual
          union all select 'CA780086AP98', 1 from dual
          union all select 'Not present in list', 1 from dual )
   , ft_t_issu as ( select 1 instr_id, 10 instr_issr_id from dual
          union all select 1, 20 from dual )
   , ft_t_irid as ( select 10 instr_issr_id, 100 issr_id from dual
          union all select 10, 200 from dual )
select a.iss_id,C.ISSR_ID
from ft_t_isid a left outer join ft_t_issu b on a.INSTR_ID=b.INSTR_ID
left outer join ft_t_irid c on b.INSTR_ISSR_ID=c.INSTR_ISSR_ID
and a.ISS_ID in ('CA13606ZDD20',
'CA780086AP98',
'AU3CB0176410',
'CND100003HM1',
'JP320045P8C8',
'CND100003CY7',
'TW0002547403',
'TW000B903ST4',
'XS0543801350')
order by a.iss_id, c.issr_id;

ISS_ID                 ISSR_ID
------------------- ----------
CA13606ZDD20               100
CA13606ZDD20               200
CA13606ZDD20
CA780086AP98               100
CA780086AP98               200
CA780086AP98
Not present in list
Not present in list

8 rows selected.

For me, the result set is correct for given query. If you want anything different, you should state exact rules which it shall fulfil. Showing expecting result set would also help in understanding them.

Although, this sample data may be invalid in your case (e.g. breaking some unique constraint). But, as you should know the data model you use, you should post it too (although it cannot be declared in the query above).
Previous Topic: External table query (compare number records in file with external table)
Next Topic: complex query
Goto Forum:
  


Current Time: Wed Aug 27 12:18:48 CDT 2014

Total time taken to generate the page: 0.33131 seconds