Home » SQL & PL/SQL » SQL & PL/SQL » Output of two queries in single row having complex relationship (Oracle 9i, 10g)
Output of two queries in single row having complex relationship [message #358773] Wed, 12 November 2008 07:57 Go to next message
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 134 times)
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
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 Go to previous messageGo to next message
VIVEKSHANKARS
Messages: 6
Registered: October 2008
Junior Member
Uploading the details with examples...
  • Attachment: Details.txt
    (Size: 1.37KB, Downloaded 124 times)
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 Go to previous messageGo to next message
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>


Smile
Rajuvan.

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 Go to previous messageGo to next message
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
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 Go to previous message
VIVEKSHANKARS
Messages: 6
Registered: October 2008
Junior Member
The below queries helped...
Thanks a lot guys Smile
Previous Topic: date format picture ends before converting entire input string
Next Topic: Extracting a substring from delimited string
Goto Forum:
  


Current Time: Sat Dec 10 01:36:24 CST 2016

Total time taken to generate the page: 0.07815 seconds