OraFAQ Forum: SQL & PL/SQL » Output of two queries in single row having complex relationship

Home » SQL & PL/SQL » SQL & PL/SQL » Output of two queries in single row having complex relationship (Oracle 9i, 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Output of two queries in single row having complex relationship [message #358773]

Wed, 12 November 2008 07:57

VIVEKSHANKARS
Messages: 6
Registered: October 2008

Junior Member

I have three tables with the relation mentioned below.
[Table with values uploaded]

Query1
Select b from (Select count(*) b from T1, T2 where T1.A = T2.X and T2.Y = ?) where b>0;
? => a value from table T3 in column L

Select T3.J, T3.K, T3.L from T3 where T3.K='K12'

I need the output in the following format using a single query
T3.J T3.K T3.L b

All the above relations hold good
Ie., T2.X contains values present in T1.A
T3.L contains values present in T2.Y

Can anyone help plzzz....

Attachment: Tables.txt
(Size: 0.37KB, Downloaded 585 times)

Report message to a moderator

Re: Output of two queries in single row having complex relationship [message #358884 is a reply to message #358773]

Wed, 12 November 2008 18:25

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

not sure what you are asking. Let me ask you this:

Quote:

If you were going to code a solution, would you accept your input as a valid specification to work from?

To start, please provide an example of desired output, and an explanation of what the basic problem is you see in getting it.

Kevin

Report message to a moderator

Re: Output of two queries in single row having complex relationship [message #358901 is a reply to message #358884]

Wed, 12 November 2008 22:18

VIVEKSHANKARS
Messages: 6
Registered: October 2008

Junior Member

Uploading the details with examples...

Attachment: Details.txt
(Size: 1.37KB, Downloaded 568 times)

Report message to a moderator

Re: Output of two queries in single row having complex relationship [message #358902 is a reply to message #358773]

Wed, 12 November 2008 22:31

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Will this help you ?

SQL> SELECT DEPTNO , ( SELECT COUNT(*)
  2                    FROM   EMP E
  3                    WHERE  E.DEPTNO = D.DEPTNO
  4                   ) cnt
  5  FROM DEPT D;

    DEPTNO        CNT
---------- ----------
        10          3
        20          5
        30          6
        40          0

SQL> SELECT * FROM (
  2  SELECT DEPTNO , ( SELECT COUNT(*)
  3                    FROM   EMP E
  4                    WHERE  E.DEPTNO = D.DEPTNO
  5                   ) CNT
  6  FROM DEPT D )
  7  WHERE  CNT >0 ;

    DEPTNO        CNT
---------- ----------
        10          3
        20          5
        30          6

SQL>

Rajuvan.

Report message to a moderator

Re: Output of two queries in single row having complex relationship [message #358943 is a reply to message #358773]

Thu, 13 November 2008 00:55

danish_fsd@yahoo.com
Messages: 38
Registered: February 2008
Location: Pakistan

Member

Hi,

Here is two solution, i think 2nd is more appropriate.

1st

select * from
(Select T3.J, T3.K, T3.L from T3 where T3.K='k11') a,
(Select b from (Select count(*) b from t1, t2 where T1.A = T2.X and T2.Y = 'y11')
where b>0) b

2nd

Select t3.j, t3.k, t3.l, count(*) b from t1, t2, t3
where T1.A = T2.X
and t2.y=t3.l
and T2.Y = (select l from t3 where t3.k='k11')
group by t3.j, t3.k, t3.l

Regards

Danish

Report message to a moderator

Re: Output of two queries in single row having complex relationship [message #359006 is a reply to message #358902]

Thu, 13 November 2008 05:16

VIVEKSHANKARS
Messages: 6
Registered: October 2008

Junior Member

The below queries helped...
Thanks a lot guys Smile

Report message to a moderator

Previous Topic:	date format picture ends before converting entire input string
Next Topic:	Extracting a substring from delimited string

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 20:35:54 CDT 2024