Home » SQL & PL/SQL » SQL & PL/SQL » left outer join query.
left outer join query. [message #218096] Tue, 06 February 2007 16:36 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
Hi,
In a two table outer join (TEST and TEST1)
if there are multiple occurences of the join condition in table TEST1, how to select only the 1st occurence from table TEST1.

For eg.
Table TEST has following data.
1 01-JAN-06
1 01-JAN-06
1 01-JAN-06
1 01-JAN-06
1 01-JAN-06
1 01-JAN-06
1 04-DEC-06
2 06-FEB-07

Table TEST1 has following data
1 system1
1 system2
3 system2

I want to display only 1 row for value 1 in table B (it can be the 1st row only) but I get 2 rows for the same with the query I tried.

select t.a,t1.b from (
select a,count(*) from test
group by a
having count(*) > 0 ) t left outer join test1 t1 on (t.a=t1.a)


The O/P is
-------------------
1 system1
1 system2
2

But the required output is
------------------------
1 system1
2



Re: left outer join query. [message #218105 is a reply to message #218096] Tue, 06 February 2007 19:13 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT *
FROM (
  SELECT DISTINCT a
  FROM test
)
LEFT JOIN (
  SELECT a, max(b) AS b
  FROM test1
  GROUP BY a
) USING (a)


Ross Leishman
Re: left outer join query. [message #218285 is a reply to message #218105] Wed, 07 February 2007 10:51 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Thanks. How to change the query if I need the count as well.
(I apologize for the changes, but the requirement keeps changing frequently).

select t.a,t1.b,cnt from (
select a,count(*) cnt from test
group by a
having count(*) > 0 ) t left outer join test1 t1 on (t.a=t1.a)

O/p
A B CNT
--------- ------------------------------ ----------
1 system1 7
1 system2 7
2 1

Required O/P
A B CNT
--------- ------------------------------ ----------
1 system1 7
2 1
Re: left outer join query. [message #218328 is a reply to message #218096] Wed, 07 February 2007 14:59 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try following:

SELECT t.a, t.cnt,
  ( SELECT b FROM test1 WHERE test1.a = t.a AND ROWNUM = 1 )
FROM
  ( SELECT a, COUNT(*)
    FROM test
    GROUP BY a ) t


Michael.
Previous Topic: data mismatch issue
Next Topic: some questions ?????
Goto Forum:
  


Current Time: Fri Dec 09 23:11:29 CST 2016

Total time taken to generate the page: 0.10262 seconds