Replace the content content of table 'XX' with the query you have... and check if this suits you.
ranit@XE11GR2>> ranit@XE11GR2>> with xx as( --- "just simulating your input data here" 2 select 1 id, 'peter' name, 'k' state, 'n' city from dual UNION ALL 3 select 1 id, 'peter' name, 'n' state, 'k' city from dual UNION ALL 4 select 2 id, 'stewie' name, 'k' state, 'n' city from dual UNION ALL 5 select 2 id, 'stewie' name, 'n' state, 'k' city from dual 6 ) 7 Select DISTINCT 8 id, 9 name, 10 MIN(state) over(partition by id,name order by id) STATE, 11 MIN(city) over(partition by id,name order by id) CITY 12 from 13 xx 14 where id = 1 15 UNION 16 Select DISTINCT 17 id, 18 name, 19 MAX(state) over(partition by id,name order by id) STATE, 20 MAX(city) over(partition by id,name order by id) CITY 21 from 22 xx 23 where id = 2; ID NAME S C ---------- ------ - - 1 peter k k 2 stewie n n Elapsed: 00:00:00.06
987166 wrote:Welcome to the forum!
Hello Oracle Gurus,
I am pretty new to SQL development
and am hoping someone could help me outSure. Whenever you have a question, please post CREATE TABLE and INSERT statements for your sample data.
I have an ID that repeats multiple times for 3 different tables based on some conditionAre you sure these are the results you get from the query you posted? Obviously, there are 2 rows in the state table with id=1, and 2 rows in the state table with id=1, so the result set should have 2 * 2 = 4 rows with id=1. The smae goes for id=2. This is one reason why you really need to post the sample data.
For your convenience, here you go
case when b.state=1 then 'K' else 'N' end,
case when c.city=2 then 'K' else 'N' end
from PRIM a
left outer join STATE b on a.id=b.id
left outer join CITY c on a.id=c.id
ID NAME STATE CITY
1 Peter K N
1 Peter N K
2 Stewie K N
2 Stewie N K
An extra row is coming because for Peter ,its Y for State and Y for city.What is 'Y"? All the relevant columns in the tables seem to be numbers, and the CASE expressions in the table always return 'K' or 'N', never 'Y'.
Expected ResultsWill the results always have exactly 1 row for every row in the priom table?
ID NAME STATE CITY
1 Peter K K
2 Stewie N N
How can I achieve this ?It depends on the reasons why you want those results.
I think I can do a union. but my real query actually has 140 columns . so i am curious to know if there is any other way to approach this?Again, it depends on your reasons. Describe why you want the results you want.
Is there a way to use distinct?That's one possibility. Another GROUP BY. Yet another is using the analytic ROW_NUMBER function, to guarantee that the result set only has 1 row for every row in prim. Again, I'm not sure that's what you want, and, givcen that it is, I don't know why you want the row you do, and not some other matching row. Please explain what you need to do.
Edited by: chris227 on 09.02.2013 16:09
select a.id ,a.name ,decode(max(b.id),null,'N','K') ,decode(max(c.id),null,'N','K') from PRIM a left outer join (select id from STATE where b.state=1) b on a.id=b.id left outer join (select id from CITY where c.city = 2 ) c on a.id=c.id group by a.id ,a.name