RE: join query
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "krishan.gupta@xxxxxxxx" <krishan.gupta@xxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Jan 2009 11:37:43 -0500
Actually, the query you show will not produce the results you asked for in the
initial posting. You have a B3 and a B4 in the table now, but the sample
output only shows B1, B2, and B3 twice, with no B4.
Assuming your sample output was supposed to be:
POS_ID CA BO
---------- -- --
2 C1 B1
2 C2 B2
2 B3
2 B4
Rather than:
POS_ID CA BO
---------- -- --
2 C1 B1
2 C2 B2
2 B3
2 B3
You still have a problem with rownum, and the output of your query may not be
repeatable. Sure, with a simple test case, it's repeatable, for the moment.
Try adding and deleting some data from each of your tables, see what happens.
A more robust solution may be something like this:
1 select b.pos_id,
2 a.case_id,
3 b.box_id
4 from xx_case a,
5 xx_box b
6 where a.pos_id(+) = b.pos_id
7 and substr(a.case_id(+),2,1) = substr(b.box_id,2,1)
8* order by a.case_id nulls last
SQL> /
POS_ID CA BO
---------- -- --
2 C1 B1
2 C2 B2
2 B3
2 B4
--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059
mark.bobak@xxxxxxxxxxxx
www.proquest.com
www.csa.com
ProQuest...Start here.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Krishan Gupta
Sent: Wednesday, January 21, 2009 10:13 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: join query
My apologies, I misstated it. xx_box have 4 records as follows :-.
select * from xx_box;
pos_id box_id
2 B1
2 B2
2 B3
2 B4
I also figured out how to get the output result I wanted. Here is the
query :-
select e.pos_id, d.case_id, e.box_id
from
(select rownum xc , c.* from xx_case c) d
full outer join
(select rownum xb , b.* from xx_box b) e on d.xc = e.xb
Thanks to you all for your precious time on this.
Krishan
-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxx]
Sent: Wednesday, January 21, 2009 4:31 AM
To: Krishan Gupta; oracle-l@xxxxxxxxxxxxx
Subject: RE: join query
Hmm....I think I see some pretty severe data modelling
problems/questions here, or, at the very least, an incompletely
communicated data model and desired output.
There are lots of ways to cobble together a query that will produce the
requested output, but, I don't think any of them will solve the problem.
One such query is:
1 select distinct a.pos_id, a.case_id,b.box_id from xx_case a,
xx_box b
2 where a.pos_id = b.pos_id and substr(a.case_id,2,1) =
substr(b.box_id,2,1)
3 union all
4 select 2,null,'B3' from dual
5 union all
6 select 2,null,'B3' from dual
7* order by 2 nulls last
SQL> /
POS_ID CA BO
---------- -- --
2 C1 B1
2 C2 B2
2 B3
2 B3
Strictly speaking, it *is* an answer to the question you asked. Having
seen that, please purge it from your brain! Don't even think of
implementing code like this.
My point is, we need more information, more sample data. We need to
better understand your data model and what question you're trying to
answer. For example, B3? Where'd those values come from, if they're
not in the table??
If you can more clearly communicate your data model, some sample data,
and some sample output that logically follows from that, I'm confident
that someone here will be able to help you.
Hope that helps,
-Mark
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Krishan Gupta [krishan.gupta@xxxxxxxx]
Sent: Tuesday, January 20, 2009 12:28 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: join query
Hi all..,
This may be very simple but I guess I need experts help for the
following in oracle 10g database :-
select * from xx_case;
pos_id case_id
2 C1
2 C2
select * from xx_box;
pos_id box_id
2 B1
2 B2
The only common column between these two tables is pos_id.
How do I write query from these two tables so that the output result
would be
pos_id case_id box_id
2 C1 B1
2 C2 B2
2 B3
2 B3
Thanks and Regards
Krishan
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
